Userform

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Userform

Post by jstevens »

Is it possible using VBA to create a Userform with a Listbox? I know how to do it manually as well as importing one.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Userform

Post by HansV »

In Tools > References, set a reference to the Microsoft Forms 2.0 Object Library and to Microsoft Visual Basic for Applications Extensibility 5.3.

Sample code, to be modified as you wish:

Code: Select all

Sub CreateAUserForm()
    Dim AUserForm As VBComponent
    Dim AListBox As MSForms.ListBox

    Set AUserForm = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    With AUserForm
        .Properties("Width") = 400
        .Properties("Height") = 300
        .Name = "MyForm"
        .Properties("Caption") = "A UserForm"
    End With

    Set AListBox = AUserForm.Designer.Controls.Add("Forms.ListBox.1")
    With AListBox
        .Name = "Combo1"
        .Left = 12
        .Top = 12
        .Height = 200
        .Width = 100
    End With
End Sub
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Userform

Post by jstevens »

Hans,

Thank you.
Regards,
John

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Userform

Post by snb »

But why would you want to create a Userform by Code ???

If you use this code no extra references will be needed.
It also illustrates that some properties can be set in the designer while others can't.
And that you can 'nest' With . . . End With

Code: Select all

Sub M_snb()
  With ActiveWorkbook.VBProject.VBComponents.Add(3)
    .Name = "scherm_2"
    .Properties("Width") = 400
    .Properties("Height") = 300
    .Properties("Caption") = "snb"
    .Properties("StartUpPosition") = 2
    .Properties("Tag") = "nieuw"
    .Properties("Showmodal") = True
    With .designer
      .Caption = "example"
      .Enabled = True
      .BackColor = vbRed
      .ForeColor = vbWhite
      .Font.Name = "Arial"
      .Font.Size = 12
      .SpecialEffect = 3
      .Zoom = 120
      With .Controls.Add("Forms.ListBox.1")
        .Name = "L_01"
        .Left = 12
        .Top = 12
        .Height = 200
        .Width = 100
      End With
    End With
  End With
End Sub

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Userform

Post by ChrisGreaves »

snb wrote:
12 Jan 2023, 13:06
But why would you want to create a Userform by Code ???
I can't speak for JStevens, but I looked at this about 10-20 years ago.
I was in the business of developing user applications quickly.
I made use of the INI file with my VBA code that used generated constants for default values. This meant that the user could delete the INI file and the application would return to default values the instant the INI was deleted; much as MSWord re-creates a default Normal.dot.
This meant that as the program was developed I had an INI file that grew day by day.

I reasoned that I could use the contents of the INI file to manufacture a user form automatically, and that user form would be guaranteed to have a control for every variable in the application environment.
The user form would be ugly, but it would be functional, and that would allow Alpha, Beta testing to carry on in parallel with, but lagging behind, development.

Over the years I have learned that most of the times that a developer asks for a feature, that feature will have a use :grin:

Cheers, Chris
An expensive day out: Wallet and Grimace

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Userform

Post by jstevens »

snb wrote:
12 Jan 2023, 13:06
But why would you want to create a Userform by Code ???
Similar to Chris' needs I wanted to recreate a workbook (XLSM) from scratch using VBA code including objects, VBA Project modules and etc.. Can it be done: yes. One needs the XLSM format if saving the workbook containing a userform and macros in the VBA Project.

Part of the exercise, was to copy VBA Project modules from a central repository, create modules and routines on the fly and .... Chip Pearson's Programming with the VBA Editor was helpful.

Thanks for the "nested" code example.
Regards,
John

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Userform

Post by snb »


jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Userform

Post by jstevens »

snb,

Thank you for the link.
Regards,
John