VBA Dynamic ComboBoxs to be added to an Excel UserFrom

I have some challenge that I try to figure out how to solve it:

I want to make dynamic UserForm with depended combo boxes which the first one will be the “Father” combo box but the “Child(S)” are changing rely on the sheet headers.

let’s say that on column A I have list of all the “Father” names and from column B1 onward (C1, D1 etc.’) I have the child of each father.

because I need a dynamic solution, I have few files with different “child’s”,

Is it possible the add combo boxes to UserForm, as many as the child’s (i.e: Header rows) ?

the UserForm , of course, will have to adjust (resize) according to the number of ComboBoxes

appreciate your assistances.

Top Contributor Asked on November 20, 2016 in VBA: User Forms.
Add Comment
6 Answer(s)
Best answer

Dear PNRao

Sorry my friend to get back to this issue but have 2 issues that i’m straggling a few days already to solve but I really don’t know what i’m doing wrong…?

I’ll very appreciate your help.

1.

In continue of your above code , I could not figure out why the following procedure, to transfer ComboBox’s values into the worksheet called AccIndex (on LastRow) do not work ?

The strange thing is that it doesn’t do the job in one hand, and doesn’t pup-up any error massage, either.  very strange….

 

This is the code i’me using:

‘****************************************************************

Private Sub cmdUpdateSheet_Click()
Dim emptyRow As Long
‘Dim ctrl As Control
Dim ctrlType As String
emptyRow = WorksheetFunction.CountA(Sheets(“AccIndex”).Range(“A:A”)) + 1 ‘Find the Last Row on the sheet on column A
‘ThisWorkbook.Worksheets(“AccIndex”).Range(“A” & emptyRow) = cmbMainGroup.Value

‘What control type to loop through
ctrlType = “Combobox”

With Worksheets(“AccIndex”).Range(“A1”)
counter = 0
‘——1st try ——————

‘Loop Through each control on UserForm
‘ For Each ctrl In Me.Controls
‘ ‘Narrow down to specific type
‘ If TypeName(ctrl) = ctrlType Then
‘ If ctrl.Value <> “” Then
‘ .Offset(0, counter + 1).Value = ctrl.Value
‘ Else: MsgBox “You should fill out all Combo Box’s”
‘ ctrl.SetFocus
‘ End If
‘ End If
‘ Next ctrl
‘End With
‘——1st try ——————

‘——2nd try ——————
Dim ctlControl As MSForms.Control
For Each ctlControl In Me.Controls

Select Case TypeName(ctlControl)
Case “ComboBox”
Worksheets(“AccIndex”).Range(“A1”) _
.Offset(0, counter + 1).Value = ctlControl.Value
End Select
Next ctlControl

‘——2nd try ——————
End With
Unload Me

‘ The dynamic UserForm I asked about is a user form that is showed up on DubleClick event of ListBox1 on UserFrom1,

‘In order to show the UserForm1 with an updated values in ListBox1, I’m calculated the sheet and re-load UserForm1
Calculate

Load UserForm1

End Sub

‘****************************************************************************************

 

2.

I’ve couldn’t figure out how to adjust this piece of your code to take the .Caption of Labals from worksheet headers (Row 1):

'Step 2: Create ComboBoxes (should be mention:Labels) for Sub Groups

 

For iCntr = 2 To UBound(myTable, 2) ' for each sub group

 

 Set objSubGroupLabel = Me.Controls.Add("Forms.label.1")

 

 With objSubGroupLabel

 

 .Name = "lblSBG_" & iCntr

 

 .Caption = "Sub-Group " & iCntr - 1 '>> Tried to adjust to header row.... 

 

 .TextAlign = 3

 

 .Left = Me.lblMainGroup.Left

 

 .Width = Me.lblMainGroup.Width

 

 .Height = Me.cmbMainGroup.Height

 

 .Top = Me.cmbMainGroup.Top + (iCntr * Me.cmbMainGroup.Height) + (iCntr * 5) + 10

 

 End With
Top Contributor Answered on December 3, 2016.

Dear

I’ve sent the file to the above mention email.

Thanks a lot !!

Regards,
Shlomi

on December 4, 2016.
Add Comment

Hi, I have tried to create the UserForm which is close to your requirement, I have’t understand the need of individual combo boxes for each Children. However, You can change the below code if required.

VBA Dynamic ComboBoxs to be added to an Dynamic UserForm

And here is the code to create Dynmic UserForms with the Dynamic ComboBoxes with Event Handling Code:

Remember to Add the References before executing the Code.

'Add Reference To:
'Microsoft Forms 2.0 Object Library
'Microsoft Visual Basic For Application Extensibility 5.3
Sub VBAF1_sbDynmicUserFormWithDynamicConboBoxes()
Dim objDynmForm As Object
Dim NewFrame As MSForms.Frame
Dim NewButton As MSForms.CommandButton
Dim objDynmComboBoxFathers As MSForms.ComboBox
Dim objDynmComboBoxChildren 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 objDynmForm = ThisWorkbook.VBProject.VBComponents.Add(3)
'Step 1: Create UserForm
With objDynmForm
.Properties("Caption") = "My Dynamic User Form"
.Properties("Width") = 400
.Properties("Height") = 400
End With
'Step 2: Add CoboBox for Fathers
Set objDynmComboBoxFathers = objDynmForm.designer.Controls.Add("Forms.combobox.1")
With objDynmComboBoxFathers
.Name = "cmbFather"
.Top = 20
.Left = 20
.Width = 200
.Height = 25
End With
'Step 3: Add CoboBox for Children
Set objDynmComboBoxChildren = objDynmForm.designer.Controls.Add("Forms.combobox.1")
With objDynmComboBoxChildren
.Name = "cmbChildren"
.Top = 75
.Left = 20
.Width = 200
.Height = 25
End With
'Step 4: Add code for Populating Fathers ComboBox in UserForm_Initialize Event Handler
objDynmForm.CodeModule.InsertLines 1, "Private Sub UserForm_Initialize()"
objDynmForm.CodeModule.InsertLines 2, "lRow = Cells(Rows.Count, ""A"").End(xlUp).Row"
objDynmForm.CodeModule.InsertLines 3, "For iCntr = 1 To lRow"
objDynmForm.CodeModule.InsertLines 4, " cmbFather.AddItem Cells(iCntr, 1)"
objDynmForm.CodeModule.InsertLines 5, "Next"
objDynmForm.CodeModule.InsertLines 6, "End Sub"
'Step 5: Add code for ComboBox Change (Fathers) Event Handler to Populate the Children
objDynmForm.CodeModule.InsertLines 7, "Private Sub cmbFather_Change()"
objDynmForm.CodeModule.InsertLines 8, " cmbChildren.Clear"
objDynmForm.CodeModule.InsertLines 9, " For iCntr=2 to 20"
objDynmForm.CodeModule.InsertLines 10, " If Cells(cmbFather.ListIndex+1, iCntr)="""" then Exit For"
objDynmForm.CodeModule.InsertLines 11, " cmbChildren.Additem Cells(cmbFather.ListIndex+1, iCntr)"
objDynmForm.CodeModule.InsertLines 12, " Next"
objDynmForm.CodeModule.InsertLines 13, "End Sub"
'Step 6: Now Show the Dynmic Form
VBA.UserForms.Add(objDynmForm.Name).Show
End Sub

 

And here is the Output you will get:

VBA Dynamic ComboBoxs to be added to an Dynamic UserFrom Output

Thanks!

Keymaster Answered on November 20, 2016.

Dear,

Thanks a lot for your time and efforts.

it wasn’t exactly what i meant….

I’m attaching a file which will clear the matter.

the first part of depending combo list , is as you advised.

the second part of adding dynamic combo boxes as many as sub-group is still a mastery to me.

in the attached file i have 3 sub-groups. (3 columns)

in other file i have 10 sub-groups. (10 columns)

I wand the user form to be update automatically on run time and add the depending combo boxes according to the sub-groups on the worksheet.

https://dl.dropboxusercontent.com/u/94656810/Example_Comboboxes.xlsm

RE: VBA Dynamic ComboBoxs to be added to an Excel UserFrom

on November 23, 2016.
Add Comment

Dear,

Thanks a lot for your time and efforts.

it wasn’t exactly what i meant….

I’m attaching a file which will clear the matter.

the first part of depending combo list , is as you advised.

the second part of adding dynamic combo boxes as many as sub-group is still a mastery to me.

in the attached file i have 3 sub-groups. (3 columns)

in other file i have 10 sub-groups. (10 columns)

I wand the user form to be update automatically on run time and add the depending combo boxes according to the sub-groups on the worksheet.

https://dl.dropboxusercontent.com/u/94656810/Example_Comboboxes.xlsm

RE: VBA Dynamic ComboBoxs to be added to an Excel UserFromdepending ComboBoxes

 

 

Top Contributor Answered on November 20, 2016.
Add Comment

Any help will be appreciated !

Top Contributor Answered on November 23, 2016.
Add Comment

Hi, Thanks for providing the detailed explanation and the example file.

Dynamic ComboBoxs to be added to an Excel UserFrom Solved

Here is the updated code, I am forwarding the updated file to your email. I have added a new worksheet, you can test it in both sheets, it will be dynamic now.

Private Sub cmbMainGroup_Change()
myTable = ActiveSheet.ListObjects(1).DataBodyRange
Set tempDict = CreateObject("Scripting.Dictionary")
With tempDict
For jCntr = 2 To UBound(myTable, 2) ' for each sub group
Set comboTemp = Me.Controls("cmbSBG_" & jCntr)
For iCntr = 1 To UBound(myTable, 1)
If myTable(iCntr, 1) = Me.cmbMainGroup And Not .Exists(myTable(iCntr, jCntr)) Then .Add myTable(iCntr, jCntr), myTable(iCntr, jCntr) & "_content"
Next
comboTemp.Clear
comboTemp.List = Application.Transpose(.Keys)
tempDict.RemoveAll
Next
End With
End Sub
Private Sub UserForm_Initialize()
'Step 1: Populate the Group Combo
myTable = ActiveSheet.ListObjects(1).DataBodyRange
Set tempDict = CreateObject("Scripting.Dictionary")
With tempDict
For iCntr = 1 To UBound(myTable, 1)
If Not .Exists(myTable(iCntr, 1)) Then .Add myTable(iCntr, 1), myTable(iCntr, 1) & "_content"
Next iCntr
Me.cmbMainGroup.List = Application.Transpose(.Keys)
End With
'Step 2: Create ComboBoxes for Sub Groups
For iCntr = 2 To UBound(myTable, 2) ' for each sub group
Set objSubGroupLabel = Me.Controls.Add("Forms.label.1")
With objSubGroupLabel
.Name = "lblSBG_" & iCntr
.Caption = "Sub-Group " & iCntr - 1
.TextAlign = 3
.Left = Me.lblMainGroup.Left
.Width = Me.lblMainGroup.Width
.Height = Me.cmbMainGroup.Height
.Top = Me.cmbMainGroup.Top + (iCntr * Me.cmbMainGroup.Height) + (iCntr * 5) + 10
End With
Set objSubGroup = Me.Controls.Add("Forms.combobox.1")
With objSubGroup
.Name = "cmbSBG_" & iCntr
.Left = Me.cmbMainGroup.Left
.Width = Me.cmbMainGroup.Width
.Height = Me.cmbMainGroup.Height
.Top = Me.cmbMainGroup.Top + (iCntr * Me.cmbMainGroup.Height) + (iCntr * 5)
lastTop = .Top
End With
Next iCntr
Me.ScrollHeight = iCntr * 100
Me.ScrollBars = fmScrollBarsVertical
tempDict = Empty
End Sub

Thanks!

Keymaster Answered on November 23, 2016.

Dear PNRao

WOW ! you are awesome !!

It indeed adding the combo box as I want but the “depending” is not “filter” according to previous ComboBox (such as the user form I sent you).

Meaning,

On ComboBox1 I’ll select >> Group 2 (Column A)
than on ComboBox2 I’ll see those items:
2222-1
2224-1
2225-1
2226-1
2227-1
2228-1
from this list, I’ll select this item (for example) >> 2222-1 (Column B)

then on ComboBox3 I’ll see those 2 items (only) :
2222-2
2223-2
I’ll select this this item (for example) >> 2222-2 (Column C)

then on ComboBox4 I’ll see this item (only) :
2223-3 (Column D)

hope I explained good enough

Thanks a lot !!

and Best Regards,

on November 25, 2016.
Add Comment

Please provide the file with code, so that I can fix and revert to you.

email: info@analysistabs.com

Thanks!

Keymaster Answered on December 3, 2016.

Dear PNRao Have sent you the file. I’m attaching a link to the file you already reply on. which is much shorter (not all the project)

https://drive.google.com/file/d/0B0XGmYnhK4YmWlQ0Z0pPWWdBNlU/view?usp=sharing

on December 5, 2016.

Hi PNRao

The following Link to the solution, if anyone by chance needs it:
http://www.vbaexpress.com/forum/showthread.php?57912-Help-with-Dynamic-UserForm-in-Excel

on December 6, 2016.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.