New UserForm

Hi,

I am trying to create a new userform and i got most thing working, but when i insert my code to work when i press a button it gives me an err.

Here is what n got so far...
Public Sub Test()

Dim TempForm As Object
Dim NewCommandButton As MSForms.CommandButton
Dim NewLabel As MSForms.Label
Dim NewCheckBox As MSForms.CheckBox

Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)

Set NewLabel = TempForm.designer.Controls.Add("Forms.label.1")
With NewLabel
.Name = "FieldLabel1"
.Caption = "My Label"
.Top = 6
.Left = 30
.Width = 150
.Height = 12
.Font.Size = 7
.Font.Name = "Tahoma"
.BackColor = &H80FFFF
.Caption = "Check Me"
End With

Set NewCheckBox = TempForm.designer.Controls.Add("Forms.checkbox.1")
With NewCheckBox
.Name = "MyCheck1"
.Caption = ""
.Top = 6
.Left = 6
.Width = 12
.Height = 12
.Font.Size = 7
.Font.Name = "Tahoma"
.BackColor = &HFF00&
End With

Set NewCommandButton = TempForm.designer.Controls.Add("Forms.CommandButton.1")
With NewCommandButton
.Name = "CommandButton1"
.Caption = "OK"
.Top = 24
.Left = 6
.Width = 108
.Height = 24
End With

Set NewCommandButton = TempForm.designer.Controls.Add("Forms.CommandButton.1")
With NewCommandButton
.Name = "CommandButton2"
.Caption = "Cansel"
.Top = 24
.Left = 120
.Width = 108
.Height = 24
End With

With TempForm
.Properties("Caption") = "Carry Over..."
.Properties("Height") = 72
End With

With TempForm.codemodule
Line = .countoflines
.insertlines Line + 1, "Private Sub CommandButton2_Click()"
.insertlines Line + 2, "userform1.Hide"
.insertlines Line + 4, "End Sub"
End With

VBA.UserForms.Add(TempForm.Name).Show

End Sub

when i press 'Cansel' (CommandButton2) i get an 402 err

'Must Close Top Most modal form first'

Can anyone help me pls

In your TempForm.codemodule, change the line
.insertlines Line + 2, "userform1.Hide"
to
.insertlines Line + 2, "Me.Hide"
This should now work. Although, remember that the form is still loaded in memory. Also, everytime you run this code, a new form will be created. this could make your project rather large.

tanks

hi thank for the quick reply, now that that problem is sorted im trying to write a code for button 1 "OK"

With TempForm.codemodule
Line = .countoflines
MyScript(0) = "If Me.CheckBox" & b & ".Value = True Then"
MyScript(1) = "If Me.Label" & b & ".Caption = Worksheets("Trinity").Cells(i, 2) Then"
.insertlines Line + 1, "Private Sub CommandButton1_Click()"
For t = 1 To b
.insertlines Line + 2, MyScript(0)
.insertlines Line + 3, "For j = 2 To LastRow"
.insertlines Line + 4, MyScript(1)
.insertlines Line + 5, "For a = 1 To 4"
.insertlines Line + 6, "Worksheets("Oorgedra").Cells(LastRowOordraOfset, a) = Worksheets("Trinity").Cells(i, a)"
.insertlines Line + 7, "Worksheets("Trinity").Cells(j, 1).Select"
.insertlines Line + 8, "ActiveCell.EntireRow.Delete"
.insertlines Line + 9, "j = j - 1"
.insertlines Line + 10, "End If"
.insertlines Line + 11, "Next j"
.insertlines Line + 12, "End If"
Next t
End With

But i seem to have a problem with double inverted commas, is there a way around it?

Hi, apologies for the delay I've not been near a pc the last couple of days.

When you're trying to use inverted commas within inverted commas, as you are above, you need to double quote the internal commas. So for example, the line that reads .insertlines Line + 6, "Worksheets("Oorgedra").Cells(LastRowOordraOfset, a) = Worksheets("Trinity").Cells(i, a)"will need to be changed to.insertlines Line + 6, "Worksheets(""Oorgedra"").Cells(LastRowOordraOfset, a) = Worksheets(""Trinity"").Cells(i, a)"Notice the double quotes around the words "Oorgedra" and "Trinity". These are your internal quotes however, the surrounding quotes remain singular. When you do this, the program knows to treat those quotation marks differently from what it normally would. If you go through your code and do this for each line that requires is, your code should now run.

Thanks!^^

Is it posible to remove/delete a userform with vba?

I tried a couple diffrent ways but keep getting an err.

ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm

Yes, you must first reference the VBComponent object, assign the form you want to delete to it then delete e.g.Sub RemoveForm()
' Dimension memory space
Dim VBC As VBComponent

' Assign object
Set VBC = ThisWorkbook.VBProject.VBComponents("UserForm1")

' Remove object
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=VBC

' Clear memory space
Set VBC = Nothing
End Sub

Thank you, i got it working.

it seem you have to put the code in the right order. haha