Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating the Creation of Userforms in VBA
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating the Creation of Userforms in VBA
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 Noel wrote: 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.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating the Creation of Userforms in VBA
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) "Noel" wrote in message oups.com... 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.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating the Creation of Userforms in VBA
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) "JW" wrote in message ups.com... 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 Noel wrote: 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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating the Creation of Userforms in VBA
On 8 Oct, 16:53, "Chip Pearson" wrote:
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 Consultingwww.cpearson.com (email on the web site) "JW" wrote in message ups.com... 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 Noelwrote: I am trying to create a semi-automated system usingVBAinExceland 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....- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Guidance on automating the creation of Excel files from Excel | Excel Programming | |||
Help with automating creation of Pie Chart(s) | Charts and Charting in Excel | |||
Automating hyperlink creation | Excel Worksheet Functions | |||
Automating production book creation - many procedures to run | Excel Programming | |||
Set Focus on Control at Creation in Dialogs, I mean UserForms | Excel Programming |