Populate other combo boxes based on previous combo boxes selection

P

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Currently on a user form, I have 6 combo boxes. The first combo box gets its values from a spreadsheet. The first combo box pulls from the code below. What I am wanting to do is to have the other combo boxes remove any name that was already chosen from the previous combo box. Is this possible? Thank you.

See code below to see how its populated.
VBA Code: 
Private Sub UserForm_Initialize()
    ComboBox1.RowSource = "Employees!A2:A" & Range("A" & Rows.Count).End(xlUp).Row
End Sub
 
Sort by date Sort by votes
Pookiemeister said:
Should the second line read: Set e = CreateObject("scripting.dictionary"): e.CompareMode = vbTextCompare
Click to expand...
Ah, you're right, it should.

Pookiemeister said:
Here is all the code for this project. I am hoping their is something in this code that is not allowing your code to work.
Click to expand...
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
 
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
@Akuini Here is a layout of my userform1 and the code that goes with it. Due to company restrictions with online access, I am unable to access dropbox or any other similar site. Thank you for your assistance.

CW_Userform1_Capture.JPG

VBA Code: 
Private Sub Workbook_Open()
    UserForm1.Show
End Sub
Code: 
Option Explicit
Dim d As Object, e As Object
Dim va As Variant

Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub btnClear_Click()
    
        
End Sub

Private Sub ComboBox1_Enter()
    Call toPopulate(1)
End Sub

Private Sub ComboBox10_Enter()
    Call toPopulate(10)
End Sub

Private Sub ComboBox11_Enter()
    Call toPopulate(11)
End Sub

Private Sub ComboBox12_Enter()
    Call toPopulate(12)
End Sub

Private Sub ComboBox13_Enter()
    Call toPopulate(13)
End Sub

Private Sub ComboBox14_Enter()
    Call toPopulate(14)
End Sub

Private Sub ComboBox15_Enter()
    Call toPopulate(15)
End Sub

Private Sub ComboBox16_Enter()
    Call toPopulate(16)
End Sub

Private Sub ComboBox17_Enter()
    Call toPopulate(17)
End Sub

Private Sub ComboBox18_Enter()
    Call toPopulate(18)
End Sub

Private Sub ComboBox19_Enter()
    Call toPopulate(19)
End Sub

Private Sub ComboBox2_Enter()
    Call toPopulate(2)
End Sub

Private Sub ComboBox20_Enter()
    Call toPopulate(20)
End Sub

Private Sub ComboBox21_Enter()
    Call toPopulate(21)
End Sub

Private Sub ComboBox22_Enter()
    Call toPopulate(22)
End Sub

Private Sub ComboBox23_Enter()
    Call toPopulate(23)
End Sub

Private Sub ComboBox24_Enter()
    Call toPopulate(24)
End Sub

Private Sub ComboBox25_Enter()
    Call toPopulate(25)
End Sub

Private Sub ComboBox26_Enter()
    Call toPopulate(26)
End Sub

Private Sub ComboBox27_Enter()
    Call toPopulate(27)
End Sub

Private Sub ComboBox28_Enter()
    Call toPopulate(28)
End Sub

Private Sub ComboBox29_Enter()
    Call toPopulate(29)
End Sub

Private Sub ComboBox3_Enter()
    Call toPopulate(3)
End Sub

Private Sub ComboBox30_Enter()
    Call toPopulate(30)
End Sub

Private Sub ComboBox31_Enter()
    Call toPopulate(31)
End Sub

Private Sub ComboBox32_Enter()
    Call toPopulate(32)
End Sub

Private Sub ComboBox4_Enter()
    Call toPopulate(4)
End Sub

Private Sub ComboBox5_Enter()
    Call toPopulate(5)
End Sub

Private Sub ComboBox6_Enter()
    Call toPopulate(6)
End Sub

Private Sub ComboBox7_Enter()
    Call toPopulate(7)
End Sub

Private Sub ComboBox8_Enter()
    Call toPopulate(8)
End Sub

Private Sub ComboBox9_Enter()
    Call toPopulate(9)
End Sub


Private Sub TextBox1_AfterUpdate()
    
    Dim tString As String
    With TextBox1
        'Check if user put in a colon or not
        If InStr(1, .Value, ":", vbTextCompare) = 0 Then
            'If not, make string 4 digits and insert colon
            tString = Format(.Value, "0000")
            tString = Left(tString, 2) & ":" & Right(tString, 2)
          
            TextBox1.Value = Format(TimeValue(tString), "HH:MM AM/PM")
        Else
            'Otherwise, take value as given
            .Value = Format(.Value, "hh:mm AM/PM")
        End If
    End With
    
End Sub

Private Sub TextBox2_AfterUpdate()
    
    Dim tString As String
    With TextBox2
        'Check if user put in a colon or not
        If InStr(1, .Value, ":", vbTextCompare) = 0 Then
            'If not, make string 4 digits and insert colon
            tString = Format(.Value, "0000")
            tString = Left(tString, 2) & ":" & Right(tString, 2)
          
            TextBox2.Value = Format(TimeValue(tString), "HH:MM AM/PM")
        Else
            'Otherwise, take value as given
            .Value = Format(.Value, "hh:mm AM/PM")
        End If
    End With
    
End Sub

Private Sub TextBox3_AfterUpdate()
    
    Dim tString As String
    
    With TextBox3
        'Check if user put in a colon or not
        If InStr(1, .Value, ":", vbTextCompare) = 0 Then
            'If not, make string 4 digits and insert colon
            tString = Format(.Value, "0000")
            tString = Left(tString, 2) & ":" & Right(tString, 2)
          
            TextBox3.Value = Format(TimeValue(tString), "HH:MM AM/PM")
        Else
            'Otherwise, take value as given
            .Value = Format(.Value, "hh:mm AM/PM")
        End If
    End With
    
End Sub

Private Sub TextBox4_AfterUpdate()
    
    Dim tString As String
    
    With TextBox4
        'Check if user put in a colon or not
        If InStr(1, .Value, ":", vbTextCompare) = 0 Then
            'If not, make string 4 digits and insert colon
            tString = Format(.Value, "0000")
            tString = Left(tString, 2) & ":" & Right(tString, 2)
          
            TextBox4.Value = Format(TimeValue(tString), "HH:MM AM/PM")
        Else
            'Otherwise, take value as given
            .Value = Format(.Value, "hh:mm AM/PM")
        End If
    End With
    
End Sub

Private Sub txtbxDate_AfterUpdate()
    
    Dim todaysDate As String
    
    todaysDate = Format(Me.txtbxDate.Value, "long date")

    If IsDate(Me.txtbxDate.Text) Then
         Me.txtbxDate.Text = todaysDate
    End If
    
End Sub

Private Sub txtbxDate_Enter()
    Me.txtbxDate.Value = ""
End Sub

Private Sub UserForm_Initialize()
        
'    Dim ctrl As Control
'
'    For Each ctrl In UserForm1.Controls
'        If TypeName(ctrl) = "TextBox" Then ctrl.Value = ""
'        If TypeName(ctrl) = "ComboBox" Then
'            ctrl.List = Sheets("Employees").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
'        End If
'    Next

    Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
    Set e = CreateObject("scripting.dictionary"): e.CompareMode = vbTextCompare
    
    With Sheets("Employees")
        va = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
    End With

End Sub
Private Sub toPopulate(n As Long)
    
    Dim i As Long
    Dim tx As String
    Dim x
    
    d.RemoveAll
    e.RemoveAll
    For i = 1 To 6
        tx = Me.Controls("Combobox" & i).Value
        If tx <> "" And i <> n Then e(tx) = Empty
    Next
    
    If e.Count <> 0 Then
        For Each x In va
            If Not e.Exists(x) Then d(x) = Empty
        Next
    Else
        For Each x In va
            d(x) = Empty
        Next
    End If
    
    Me.Controls("Combobox" & n).List = d.keys

End Sub
 
Last edited:
Upvote 0
So, you have 32 comboboxes, their names are Combobox1, Combobox2, ......Combobox32, correct?
Change 6 to 32 in this part:
VBA Code: 
For i = 1 To 6
    tx = Me.Controls("Combobox" & i).Value
    If tx <> "" And i <> n Then e(tx) = Empty
Next

If that still doesn’t work then it's hard for me to check what cause the problem without a sample file.
Pookiemeister said:
Due to company restrictions with online access, I am unable to access dropbox or any other similar site.
Click to expand...
Can you access it via your home computer?
 
Upvote 0
@Akuini

I can, but I don't understand what the difference would be? The code that was added is still the same.
 
Last edited:
Upvote 0
@Akuini So I am not sure what happened, but items now appear in the dropdown. However, I'm getting a "Runtime error '94: Invalid use of Null" on this line.
VBA Code: 
tx = Me.Controls("Combobox" & i).Value
. I am not sure why? Thank you.

VBA Code: 
Private Sub toPopulate(n As Long)
    
    Dim i As Long
    Dim tx As String
    Dim x
    
    d.RemoveAll
    e.RemoveAll
    For i = 1 To 32
        tx = Me.Controls("Combobox" & i).Value
        If tx <> "" And i <> n Then e(tx) = Empty
    Next
    
    If e.Count <> 0 Then
        For Each x In va
            If Not e.Exists(x) Then d(x) = Empty
        Next
    Else
        For Each x In va
            d(x) = Empty
        Next
    End If
    
    Me.Controls("Combobox" & n).List = d.keys

End Sub
 
Upvote 0
Pookiemeister said:
@Akuini So I am not sure what happened, but items now appear in the dropdown. However, I'm getting a "Runtime error '94: Invalid use of Null" on this line.
VBA Code: 
tx = Me.Controls("Combobox" & i).Value
. I am not sure why? Thank you.

VBA Code: 
Private Sub toPopulate(n As Long)
   
    Dim i As Long
    Dim tx As String
    Dim x
   
    d.RemoveAll
    e.RemoveAll
    For i = 1 To 32
        tx = Me.Controls("Combobox" & i).Value
        If tx <> "" And i <> n Then e(tx) = Empty
    Next
   
    If e.Count <> 0 Then
        For Each x In va
            If Not e.Exists(x) Then d(x) = Empty
        Next
    Else
        For Each x In va
            d(x) = Empty
        Next
    End If
   
    Me.Controls("Combobox" & n).List = d.keys

End Sub
Click to expand...
I even changed the spelling of "Combobox" to "ComboBox" but that did not work either.
 
Upvote 0
Hm, I can't think of anything that can produce that kind of error.
It's better if you could upload your sample file so I can investigate the problem.
 
Upvote 0
1. You should put "Private Sub toPopulate" in userform1 module.

2. I still can't figure out why it raises the "Runtime error '94: Invalid use of Null", but I manage to resolve this issue by changing .Value property to .Text in this part:

VBA Code: 
    For i = 1 To 32
        tx = Me.Controls("Combobox" & i).Text  '''I've changed .Value to .Text
        If tx <> "" And i <> n Then e(tx) = Empty
    Next

3. At this point, I've removed all non relevant code on your sample file because I just want to show how the comboboxes work.

The file:
www.mediafire.com

Pookiemeister - CW_Schedule 2

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.
www.mediafire.com
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA - Populate listbox in userform based on combo box selection
Replies
1
Views
388
Saber75
S
S
  • Question Question
VBA Combo Box populating based on entry in another Combo Box and filtered by another column
Replies
1
Views
485
skciws
S
K
  • Solved
VBA in User Form. Xlookup formula for Dependent Combo Box.
Replies
8
Views
273
Kscoof
K
E
  • Solved
Display Lookup table from dropdown list in a form?
Replies
3
Views
328
myall_blues
myall_blues
G
  • Question Question
VBA Userform Date Format
Replies
7
Views
164
gilly01625
G

Forum statistics

Threads
1,223,705
Messages
6,173,994
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top