Automating the Creation of Userforms in VBA

[Noel]

I am trying to create a semi-automated system using VBA in Excel and
need some help in creating an interface for user input.

With each use of the tool I am building, it needs to create
automatically a different number of user forms with a varying number
of checkboxes in each one. Not only will the number of user forms
change with each use of the tool, but so also will the number of
checkboxes on each user form. Does anyone have an idea as to how to
do
this?


Thanks in advance....

 

[JW]

Sounds like a complicated program. Probably be easiest to just create
a dialogsheet instead of userforms.
Set yourDialog = ActiveWorkbook.DialogSheets.Add

Then you can add checkboxes to it like
yourDialog.CheckBoxes.Add

 

[Chip Pearson]

Are the forms really just modifications of one standard form? Will all the
forms have all the checkboxes already created? If both of these are true,
you can use code like the following. It will create 3 instances of
UserForm1, change the caption of each form and then make some checkboxes
visible and other hidden. This is just an example of one way to do it. Your
actual logic will likely be more complex.

Dim UForms() As UserForm1
Sub SetupAndShowForms()

Dim NumForms As Long
Dim N As Long
Dim UF1 As UserForm1
NumForms = 3 ' example

Erase UForms
ReDim UForms(1 To NumForms)
For N = 1 To NumForms
Set UF1 = New UserForm1
With UF1
Select Case N
Case 1
.Caption = "Form 1"
.CheckBox1.Visible = True
.CheckBox2.Visible = True
.CheckBox3.Visible = True
Case 2
.Caption = "Form 2"
.CheckBox1.Visible = True
.CheckBox2.Visible = False
.CheckBox3.Visible = True
Case 3
.Caption = "Form 3"
.CheckBox1.Visible = False
.CheckBox2.Visible = True
.CheckBox3.Visible = False

' more cases

Case Else
End Select
End With
Set UForms(N) = UF1
UF1.Show vbModeless
Next N

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Chip Pearson]

Set yourDialog = ActiveWorkbook.DialogSheets.Add

Then you can add checkboxes to it like
yourDialog.CheckBoxes.Add

You can do exactly the same thing with a UserForm rather than a DialogSheet.

With UserForm1
With .Controls.Add("Forms.Checkbox.1", "MyCheckbox1", True)
.Caption = "Check Me"
.Value = True
.Top = 50
.Left = 70
.Visible = True
End With
.Show
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Noel]

You can do exactly the same thing with a UserForm rather than a DialogSheet.

With UserForm1
With .Controls.Add("Forms.Checkbox.1", "MyCheckbox1", True)
.Caption = "Check Me"
.Value = True
.Top = 50
.Left = 70
.Visible = True
End With
.Show
End With

--
Cordially,
Chip Pearson
Microsoft MVP -Excel, 10 Years
Pearson Software Consultingwww.cpearson.com
(email on the web site)







- Show quoted text -

Hi JW and Chip,

Thanks very much for your help and advice and please accept my
apologies for the late reply. I have a feeling that I am biting off a
bit more than I can chew on the VBA front. I liked the idea but the
execution sounds a bit beyond me to be honest. I will have a play with
the above code and see how I go. Any other thoughts would be greatly
appreciated.

Cheers, Noel