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.

 

[Akuini]

Here's my suggestion:
1. After selecting a name, say in CB1, you need to confirm the selection, maybe by double-clicking the name.
2. The name will be transferred to a label (each combobox has its own label next to it)
3. The name will be removed from ALL combobox (including in CB1).
this way the list in all combobox always be the same, so it's easier to maintain, including if you make a wrong selection that needs to be corrected.
So, basically the code will check every label, if there's a name then remove the name from the list of all combobox.

 

[NoSparks]

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

I don't get any errors or I wouldn't have posted.

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.

Yup, that eliminates the use of Combos Change event in favor of the user verifying combo selection is OK.
I fully agree with Akuini post 11, especially one list for all combos.

 

[Leon445522]

Hi,

I use NoSparks code. Thank you. Thank you.
Now it works well. I uninstall then reinstall Microsoft 365.
NoSparks, if you can add some line of code to have that photo in picture place in UserForm.
Remember in column A is name (starting A2) and in column B in same row are photo.
Thank you again.
Many thanks to all who help me.

 

[NoSparks]

Glad you got it working.

I don't have your pictures and don't know exactly what you have in column B, but you can try
Changing all the combo_Changes a little to insert the pictures and also require the user to verify the name removal from the drop downs.

Private Sub ComboBox1_Change()
    calledby = "ComboBox1"
    '
    'call procedure to put in the image
    Call LoadTheImage
    '
    response = MsgBox("Happy with this name selection?", vbYesNo, "REMOVE NAME ?")
    If response = vbYes Then
        Call AlterDropDowns
    End If
End Sub

Add this procedure in the form module to get the image associated with the name.
You will require the full file path to the images.

Private Sub LoadTheImage()
    Dim fndRng As Range, FindString As String
    Dim Img As String
    Dim ws As Worksheet
    
Set ws = Sheets("Sheet2")
' name to find
FindString = Controls(calledby).Value
' find it
Set fndRng = ws.Range("A:A").Find(FindString, , xlValues, xlWhole, xlByRows, xlNext, False)
' image to load
Img = fndRng.Offset(, 1).Value
' where to load it
With Controls("Image" & Mid(calledby, 9))
    .Picture = LoadPicture(Img)
    .PictureSizeMode = 1
End With

End Sub

Hope that helps.

 

[Leon445522]

@NoSparks,

Excellent. Excellent job.
Thank you very much.