The selection from one combobox should no longer be available in the other comboboxes.

[Leon445522]

Hi,

I want to make a Userform, containing 10 pages, in each page I will have 2 comboboxes and 2 images.
The images will appear depending on what is chosen in the corresponding combobox.
The names in the combobox will be taken from sheet2, column A and the images will be in column B
I would like you to help me with a VBA code.
When choosing a name - let's say - in combobox1, that picture of that name will appear and that name will disappear from all other comboboxes.
Let's say that I choose in combobox3, name5, that picture related to that name will appear and in all other comboboxes no name5 will be available.
If I then choose name11 in combobox4, then name11 should no longer be available in all other comboboxes. So now names5 and names11 will no longer be available in all other comboboxes.

Thank you in advance.

 

[Crystalyzer]

would something like this help you setting the combobox items?

Book11
	A	B	C	D	E
1	Selected
2	Harry	Jane	John	Judy
3
4	Name1	Name2	Name3	Name4	Name5
5	Harry	Jane	Joe	Joe	Joe
6	Jane	Joe	John	Judy	Mary
7	Joe	John	Judy	Mary	Scott
8	John	Judy	Mary	Scott
9	Judy	Mary	Scott
10	Mary	Scott
11	Scott
12
Sheet8

Cell Formulas
Range	Formula
B5:B10	B5	=FILTER($A$5:$A$11,$A$5:$A$11<>$A$2:A2)
C5:C9,E5:E7,D5:D8	C5	=FILTER(B5#,B5#<>B2)
Dynamic array formulas.

 

[Leon445522]

Thanks for reply but I need VBA code.
I can make Userform with multitab and in every tab, 2 combobox and 2 place for image.

 

[Leon445522]

Hi,

It's impossible to make it?
Please, need an advice or some ideea to make it work.

Thanks.

 

[Leon445522]

Nobody???

 

[Crystalyzer]

I have devised a possible solution you can run with leveraging my previous suggestion. The worksheet setup is as follows:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Selected
2
3
4	Name1	Image	Name2	Name3	Name4	Name5	Name6	Name7	Name8	Name9	Name10
5	Alex		Alex	Alex	Alex	Alex	Alex	Alex	Alex	Alex	Alex
6	Harry		Harry	Harry	Harry	Harry	Harry	Harry	Harry	Harry	Harry
7	Iris		Iris	Iris	Iris	Iris	Iris	Iris	Iris	Iris	Iris
8	Jane		Jane	Jane	Jane	Jane	Jane	Jane	Jane	Jane	Jane
9	Jim		Jim	Jim	Jim	Jim	Jim	Jim	Jim	Jim	Jim
10	Joe		Joe	Joe	Joe	Joe	Joe	Joe	Joe	Joe	Joe
11	John		John	John	John	John	John	John	John	John	John
12	Judy		Judy	Judy	Judy	Judy	Judy	Judy	Judy	Judy	Judy
13	Kevin		Kevin	Kevin	Kevin	Kevin	Kevin	Kevin	Kevin	Kevin	Kevin
14	Mary		Mary	Mary	Mary	Mary	Mary	Mary	Mary	Mary	Mary
15	Murray		Murray	Murray	Murray	Murray	Murray	Murray	Murray	Murray	Murray
16	Scott		Scott	Scott	Scott	Scott	Scott	Scott	Scott	Scott	Scott
Sheet7

Cell Formulas
Range	Formula
C5:C16	C5	=FILTER(Name1,Name1<>A2)
D5:K16	D5	=FILTER(C5#,C5#<>C2)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Name1	=Sheet7!$A$5:$A$16	C5
Name1Sel	=Sheet7!$A$2	C5
Name2	=Sheet7!$C$5#	D5
Name2Sel	=Sheet7!$C$2	D5
Name3	=Sheet7!$D$5#	E5
Name3Sel	=Sheet7!$D$2	E5
Name4	=Sheet7!$E$5#	F5
Name4Sel	=Sheet7!$E$2	F5
Name5	=Sheet7!$F$5#	G5
Name5Sel	=Sheet7!$F$2	G5
Name6	=Sheet7!$G$5#	H5
Name6Sel	=Sheet7!$G$2	H5
Name7	=Sheet7!$H$5#	I5
Name7Sel	=Sheet7!$H$2	I5
Name8	=Sheet7!$I$5#	J5
Name8Sel	=Sheet7!$I$2	J5
Name9	=Sheet7!$J$5#	K5
Name9Sel	=Sheet7!$J$2	K5
SelectedNames	=Sheet7!$A$2:$K$2	C5

the VBA code is

Dim cLoc As Range
Dim ws As Worksheet
    
Private Sub UserForm_Initialize()
    Set ws = Worksheets("Sheet7")
    
    ws.Range("SelectedNames") = ""
    
    
    For Each cLoc In ws.Range("Name1")
        With Me.ComboBox1
            .AddItem cLoc.Value
        End With
    Next cLoc
End Sub

Private Sub ComboBox1_Change()
        
    ws.Range("Name1Sel") = Me.ComboBox1.Value
    
    For Each cLoc In ws.Range("Name2")
        Me.ComboBox2.AddItem cLoc.Value
    Next cLoc
    
End Sub

Private Sub ComboBox2_Change()
    
    ws.Range("Name2Sel") = Me.ComboBox2.Value
    
    For Each cLoc In ws.Range("Name3")
        Me.ComboBox3.AddItem cLoc.Value
    Next cLoc
    
End Sub

Private Sub ComboBox3_Change()
    
    ws.Range("Name3Sel") = Me.ComboBox3.Value
    
    For Each cLoc In ws.Range("Name4")
        Me.ComboBox4.AddItem cLoc.Value
    Next cLoc
    
End Sub

Private Sub ComboBox4_Change()
    
    ws.Range("Name4Sel") = Me.ComboBox4.Value
    
    For Each cLoc In ws.Range("Name5")
        Me.ComboBox5.AddItem cLoc.Value
    Next cLoc
    
End Sub

Private Sub ComboBox5_Change()
    
    ws.Range("Name5Sel") = Me.ComboBox5.Value
    
    For Each cLoc In ws.Range("Name6")
        Me.ComboBox6.AddItem cLoc.Value
    Next cLoc
    
End Sub

Private Sub ComboBox6_Change()
    
    ws.Range("Name6Sel") = Me.ComboBox6.Value
    
    For Each cLoc In ws.Range("Name7")
        Me.ComboBox7.AddItem cLoc.Value
    Next cLoc
    
End Sub

Private Sub ComboBox7_Change()
    
    ws.Range("Name7Sel") = Me.ComboBox7.Value
    
    For Each cLoc In ws.Range("Name8")
        Me.ComboBox8.AddItem cLoc.Value
    Next cLoc
    
End Sub

Private Sub ComboBox8_Change()
     
    ws.Range("Name8Sel") = Me.ComboBox8.Value
    
    For Each cLoc In ws.Range("Name9")
        Me.ComboBox9.AddItem cLoc.Value
    Next cLoc
    
End Sub

Private Sub ComboBox9_Change()
    
    ws.Range("Name9Sel") = Me.ComboBox9.Value
    
    For Each cLoc In ws.Range("Name10")
        Me.ComboBox10.AddItem cLoc.Value
    Next cLoc
    
End Sub

In the picture below you can see that the dropdown for ComboBox4 only includes the names in Name4 range

 

[NoSparks]

VBA suggestion
Load all 20 comboboxes from UserForm_Initialize to start with

Private Sub UserForm_Initialize()
    Dim lr As Long, ctrl As Control, Arr As Variant
    
With Sheets("Sheet2")
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    Arr = .Range("A2:A" & lr)
End With

' populate combos with  arr
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "ComboBox" Then
        ctrl.List = Arr
    End If
Next ctrl

Set Arr = Nothing

End Sub

Create a global variable and assign a value in the Change event of each combo box for who is calling a common macro

Option Explicit

    Dim calledby As String
    '

Private Sub ComboBox1_Change()
    calledby = "ComboBox1"
    Call AlterDropDowns
End Sub

Private Sub ComboBox2_Change()
    calledby = "ComboBox2"
    Call AlterDropDowns
End Sub

Private Sub ComboBox3_Change()
    calledby = "ComboBox3"
    Call AlterDropDowns
End Sub

'''' etc. for all 20 combos

And in a common procedure loop through the combos, loading their existing drop down into a dictionary
remove the selected name and write the dictionary back to the combo list

Private Sub AlterDropDowns()
    Dim i As Long
    Dim ctrl As Control
    Dim Dic As Object
    Dim ray
    
' to hold current drop down
Set Dic = CreateObject("Scripting.Dictionary")
' loop through combos
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "ComboBox" And ctrl.Name <> calledby Then
        ' get current drop down list as array
        ray = Application.Transpose(ctrl.List)
        ' feed array into dictionary
        For i = LBound(ray) To UBound(ray)
          Dic(ray(i)) = 1
        Next i
        ' remove the selection of calledby from dictionary
        Dic.Remove Controls(calledby).Value
        ' apply dictionary back to combo list
        ctrl.List = Dic.keys
    End If
    ' empty the dictionary for next go round
    Dic.RemoveAll
Next ctrl

End Sub

This doesn't have any error trapping or checks of any kind so if you remove all the names it will likely crash.
Any how... just my way of doing things. my test file

 

[Akuini]

@Leon445522
Is it possible that in one combo box you choose the name more than once?
For example, in combo box3 you select name5 and then later you select name6. After you select name6, should name5 be removed from combobox3? since name5 has been selected it should not appear again.

 

[Leon445522]

@Crystalyzer,

Can't make it work.

@NoSparks,
Thanks for reply,

Something I do wrong. With your file I get this Error:
Microsoft Visual Basic for Application
Automation Error
Exception occurred

If press OK button Excel is restarted.

 

[Leon445522]

@Leon445522
Is it possible that in one combo box you choose the name more than once?
For example, in combo box3 you select name5 and then later you select name6. After you select name6, should name5 be removed from combobox3? since name5 has been selected it should not appear again.

in combo box3 you select name5 and then later you select name6. After you select name6, should name5 be removed from combobox3?

No.

Try to explain better.
Let say in (ComboBox) CB1 I choose Name8 (that picture with Name8 will appear in pictures place, near CB1, and that name will disappear from all other comboboxes)
If I change my mind (say I put Name8 by mistake) and I will change with -say- Name2 then only Name2 will disappear from all other comboboxes. Name8 will be available.

If this is difficult, then I will start always, to choose name, starting with CB1, then CB2 (here will be all name except name choose in CB1), in CB3 the name available will be, all name, except name chooses in CB1 and CB2 and so on.
I will accept any other ideea.

Thank you.