Creating form programmatically in the module using vba

by

After hardwork I have found a very simple answer to my question. May help you also.

Sub CreateUserForm()
Dim myForm As Object
Dim NewFrame As MSForms.Frame
Dim NewButton As MSForms.CommandButton
'Dim NewComboBox As MSForms.ComboBox
Dim NewListBox As MSForms.ListBox
'Dim NewTextBox As MSForms.TextBox
'Dim NewLabel As MSForms.Label
'Dim NewOptionButton As MSForms.OptionButton
'Dim NewCheckBox As MSForms.CheckBox
Dim X As Integer
Dim Line As Integer

'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False

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

'Create the User Form
With myForm
    .Properties("Caption") = "New Form"
    .Properties("Width") = 300
    .Properties("Height") = 270
End With

'Create ListBox
Set NewListBox = myForm.designer.Controls.Add("Forms.listbox.1")
With NewListBox
    .Name = "lst_1"
    .Top = 10
    .Left = 10
    .Width = 150
    .Height = 230
    .Font.Size = 8
    .Font.Name = "Tahoma"
    .BorderStyle = fmBorderStyleOpaque
    .SpecialEffect = fmSpecialEffectSunken
End With

'Create CommandButton Create
Set NewButton = myForm.designer.Controls.Add("Forms.commandbutton.1")
With NewButton
    .Name = "cmd_1"
    .Caption = "clickMe"
    .Accelerator = "M"
    .Top = 10
    .Left = 200
    .Width = 66
    .Height = 20
    .Font.Size = 8
    .Font.Name = "Tahoma"
    .BackStyle = fmBackStyleOpaque
End With

'add code for listBox
lstBoxData = "Data 1,Data 2,Data 3,Data 4"
myForm.codemodule.insertlines 1, "Private Sub UserForm_Initialize()"
myForm.codemodule.insertlines 2, "   me.lst_1.addItem ""Data 1"" "
myForm.codemodule.insertlines 3, "   me.lst_1.addItem ""Data 2"" "
myForm.codemodule.insertlines 4, "   me.lst_1.addItem ""Data 3"" "
myForm.codemodule.insertlines 5, "End Sub"

'add code for Comand Button
myForm.codemodule.insertlines 6, "Private Sub cmd_1_Click()"
myForm.codemodule.insertlines 7, "   If me.lst_1.text <>"""" Then"
myForm.codemodule.insertlines 8, "      msgbox (""You selected item: "" & me.lst_1.text )"
myForm.codemodule.insertlines 9, "   End If"
myForm.codemodule.insertlines 10, "End Sub"
'Show the form
VBA.UserForms.Add(myForm.Name).Show

'Delete the form (Optional)
'ThisWorkbook.VBProject.VBComponents.Remove myForm
End Sub

More Related Contents:

  1. Duplicate number and it value in column EXCEL [closed]
  2. Rearranging Excel Cell based on Value [closed]
  3. How do I get the old value of a changed cell in Excel VBA?
  4. Force a screen update in Excel VBA
  5. Find all matches in workbook using Excel VBA
  6. Create a new sheet for each unique agent and move all data to each sheet
  7. Set Auto Filtering multiple wildcards
  8. Protecting cells in Excel but allow these to be modified by VBA script
  9. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  10. How to make Excel VBA variables available to multiple macros?
  11. Use of symbol # (hash) in VBA Macro
  12. Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”
  13. Excel VBA looping through multiple worksheets
  14. When one should use Set [e.g for SpecialCells return value]?
  15. Excel: macro to export worksheet as CSV file without leaving my current Excel sheet
  16. Is there a faster CountIF
  17. Excel VBA How to detect if something was pasted in a Worksheet
  18. Getting error Procedure too large in VBA Macros (Excel)
  19. VBA: How to delete filtered rows in Excel?
  20. How to add data validation to a cell using VBA
  21. How to do late binding in VBA?
  22. How to break long string to multiple lines
  23. How to copy a chart from Excel to PowerPoint?
  24. vba paste not working
  25. mac excel vba loop : from list & then export as pdf
  26. Can Advanced Filter criteria be in the VBA rather than a range?
  27. How can I open this VBA workbook faster?
  28. How do I avoid run-time error when a worksheet is protected in MS-Excel?
  29. Conditional formatting using 3 conditions in Macro/VBA
  30. VBA inheritance, analog of super

Leave a Comment