remove selected item for the next drop down

G

GeneBF

New Member
Joined
Jun 28, 2022
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Hello here again.
I have this userform with multiple combo box
how can I remove Selected zone for any of the dropdown (column A and C combobox only)
example for this
I selected Zone 1 for the 1st combobox1, and zone2 for combobox2
zone 1 and 2 should not appear in combobox3.
zone 1 should also not appear in combobox2
as well as zone 2 should not appear in combobox 1
1658717518981.png


the combobox_change sub are repeated for all dependent combobox.

main ones are combobox , 1, 2, 3, 4, 5, 6, 7, and 15
ZONES2 is my named range for the list



VBA Code: 
Private Sub UserForm_initialize()

Dim rng As Range
Dim ws As Worksheet

Set ws = Worksheets("ZONING")
For Each rng In ws.Range("ZONES2")

    Me.ComboBox1.AddItem rng
    Me.ComboBox2.AddItem rng
    Me.ComboBox3.AddItem rng
    Me.ComboBox4.AddItem rng
    Me.ComboBox5.AddItem rng
    Me.ComboBox6.AddItem rng
    Me.ComboBox7.AddItem rng
    Me.ComboBox15.AddItem rng
       
    Next rng
   
    Me.ComboBox2.Enabled = False
    Me.ComboBox3.Enabled = False
    Me.ComboBox4.Enabled = False
    Me.ComboBox5.Enabled = False
    Me.ComboBox6.Enabled = False
    Me.ComboBox7.Enabled = False   
    Me.ComboBox8.Enabled = False
    Me.ComboBox9.Enabled = False
    Me.ComboBox10.Enabled = False
    Me.ComboBox11.Enabled = False
    Me.ComboBox12.Enabled = False
    Me.ComboBox13.Enabled = False
    Me.ComboBox14.Enabled = False   

End Sub



Private Sub ComboBox1_Change()


    Sheets("ZONING").Range("K37") = ComboBox1.Value   
  
   'Disable other drop down unless the previous have value
   
        If ComboBox1.Value = "" Then
       
            Me.ComboBox8.Enabled = False
            Me.ComboBox2.Enabled = False
            Me.ComboBox8.Value = ""
            Me.ComboBox2.Value = ""
           
        Else
       
            Me.ComboBox8.Enabled = True
            Me.ComboBox2.Enabled = True         
                     
        End If
                               
                     
    'Dependent list base on left dropdown
       
        With Me.ComboBox8

        Select Case ComboBox1.Value
            Case Is = "ZONE1"
            .RowSource = "ZONE1"
            Case Is = "ZONE2"
            .RowSource = "ZONE2"
            Case Is = "ZONE3"
            .RowSource = "ZONE3"
            Case Is = "ZONE4"
            .RowSource = "ZONE4"
            Case Is = "ZONE5"
            .RowSource = "ZONE5"
            Case Is = "ZONE6"
            .RowSource = "ZONE6"
            Case Is = "ZONE7"
            .RowSource = "ZONE7"
            Case Is = "ZONE8"
            .RowSource = "ZONE8"
           
     End Select
    
    End With         

End Sub
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
See if this example helps:

www.mediafire.com

dhee - userform - combobox remove previous selected items

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


VBA Code: 
Option Explicit
Dim d As Object
Dim e As Object
Dim va


Private Sub UserForm_Initialize()

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 ComboBox1_Enter()
    Call toPopulate(1)
End Sub

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

Private Sub ComboBox3_Enter()
    Call toPopulate(3)
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 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).Text
    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

dhee - userform - combobox remove previous selected items.jpg
 
Upvote 0
Solution
Akuini said:
See if this example helps:

www.mediafire.com

dhee - userform - combobox remove previous selected items

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


VBA Code: 
Option Explicit
Dim d As Object
Dim e As Object
Dim va


Private Sub UserForm_Initialize()

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 ComboBox1_Enter()
    Call toPopulate(1)
End Sub

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

Private Sub ComboBox3_Enter()
    Call toPopulate(3)
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 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).Text
    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

View attachment 70025
Click to expand...
Hi its Working Thanks so much, although there was a problem with the last combobox.
The other box changes whenever theres a selection to the other. but the last one doesnt change anymore once activated
1658743035886.png


1658743270099.png

heres the code you have adjusted to my userform

VBA Code: 
Option Explicit
Dim d As Object
Dim e As Object
Dim va


Private Sub UserForm_Initialize()

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
Set e = CreateObject("scripting.dictionary"): e.CompareMode = vbTextCompare

With Sheets("ZONING")
    va = .Range("ZONES2")
End With

   
    Me.ComboBox2.Enabled = False
    Me.ComboBox3.Enabled = False
    Me.ComboBox4.Enabled = False
    Me.ComboBox5.Enabled = False
    Me.ComboBox6.Enabled = False
    Me.ComboBox7.Enabled = False
   
    Me.ComboBox8.Enabled = False
    Me.ComboBox9.Enabled = False
    Me.ComboBox10.Enabled = False
    Me.ComboBox11.Enabled = False
    Me.ComboBox12.Enabled = False
    Me.ComboBox13.Enabled = False
    Me.ComboBox14.Enabled = False



End Sub


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

Private Sub ComboBox3_Enter()
    Call toPopulate(3)
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 ComboBox15_Enter()
    Call toPopulate(15)
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 7 And 15
    tx = Me.Controls("combobox" & i).Text
    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
It looks like you want to exclude combobox 8 to 14.
Change this part:
VBA Code: 
For i = 1 To 7 And 15
    tx = Me.Controls("combobox" & i).Text
    If tx <> "" And i <> n Then e(tx) = Empty
Next
to:
VBA Code: 
For i = 1 To 15
    If i < 8 Or i = 15 Then  'exclude 8 to 14
    tx = Me.Controls("combobox" & i).Text
    If tx <> "" And i <> n Then e(tx) = Empty
    End If
Next
 
Upvote 0
Akuini said:
It looks like you want to exclude combobox 8 to 14.
Change this part:
VBA Code: 
For i = 1 To 7 And 15
    tx = Me.Controls("combobox" & i).Text
    If tx <> "" And i <> n Then e(tx) = Empty
Next
to:
VBA Code: 
For i = 1 To 15
    If i < 8 Or i = 15 Then  'exclude 8 to 14
    tx = Me.Controls("combobox" & i).Text
    If tx <> "" And i <> n Then e(tx) = Empty
    End If
Next
Click to expand...
i see i thought the and will do, Thanks! its working great now. cheers!
 
Upvote 0
Akuini said:
It looks like you want to exclude combobox 8 to 14.
Change this part:
VBA Code: 
For i = 1 To 7 And 15
    tx = Me.Controls("combobox" & i).Text
    If tx <> "" And i <> n Then e(tx) = Empty
Next
to:
VBA Code: 
For i = 1 To 15
    If i < 8 Or i = 15 Then  'exclude 8 to 14
    tx = Me.Controls("combobox" & i).Text
    If tx <> "" And i <> n Then e(tx) = Empty
    End If
Next
Click to expand...
Akuini said:
It looks like you want to exclude combobox 8 to 14.
Change this part:
VBA Code: 
For i = 1 To 7 And 15
    tx = Me.Controls("combobox" & i).Text
    If tx <> "" And i <> n Then e(tx) = Empty
Next
to:
VBA Code: 
For i = 1 To 15
    If i < 8 Or i = 15 Then  'exclude 8 to 14
    tx = Me.Controls("combobox" & i).Text
    If tx <> "" And i <> n Then e(tx) = Empty
    End If
Next
Click to expand...
uhm wait osry i thought it was working, its still the same .
 
Upvote 0
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.
It will make it easier to investigate the problem.
 
Upvote 0
"Private Sub ComboBox15_Enter" is triggered once only. I don't know why.
But it works on "Private Sub ComboBox15_DropButtonClick".
So replace this part:
VBA Code: 
Private Sub ComboBox15_Enter()
    Call toPopulate(15)
End Sub
with this:
VBA Code: 
Private Sub ComboBox15_DropButtonClick()
    Call toPopulate(15)
End Sub
 
Upvote 0
Akuini said:
"Private Sub ComboBox15_Enter" is triggered once only. I don't know why.
But it works on "Private Sub ComboBox15_DropButtonClick".
So replace this part:
VBA Code: 
Private Sub ComboBox15_Enter()
    Call toPopulate(15)
End Sub
with this:
VBA Code: 
Private Sub ComboBox15_DropButtonClick()
    Call toPopulate(15)
End Sub
Click to expand...
alright now its 100% working as intended, thanks again! i appreciate you taking time to look on it , Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question Question
userform auto fill all
Replies
1
Views
408
Micron
M
DB73
  • Solved
Could this be written in a shorter way
Replies
8
Views
545
Fluff
Fluff
C
  • Solved
VBA - Enable Checkboxes with conditions
Replies
3
Views
871
DanteAmor
DanteAmor
M
  • Solved
Excel Userform: certain fields need to be entered
Replies
6
Views
611
Micron
M
A
  • Question Question
change code populate items for four comboboxes instead of three for each row on userfrom
Replies
3
Views
462
Abdo
A

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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