Userform
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Userform
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:
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
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
-
- 4StarLounger
- Posts: 587
- Joined: 14 Nov 2012, 16:06
Re: Userform
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
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
-
- PlutoniumLounger
- Posts: 15645
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Userform
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
Cheers, Chris
He who plants a seed, plants life.
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Userform
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
John
-
- 4StarLounger
- Posts: 587
- Joined: 14 Nov 2012, 16:06
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California