Listbox with multiple selection values to enable /disable another listbox

[Monty]

Hello Everybody.....Require your help urgently....I have two Listboxs with multiple selection of values ...When I click any value in listbox1 .....it should disable listbox2....when value is deselected from listbox1 then listbox2 should be Enabled......Looks very simple...but not finding solution...please help..

 

[WelshIan]

Hi Monty

You don't need VBA to do this.

With Listbox2, open Data Validation and in the validation criteria, allow List. You will then be able to select your Source. The Source can be a range, named range or formula.
Enter the following in the Source:
=IF(ISBLANK(F1),Range,indirect(G1))

F1 is the cell where your listbox1 is located, so change this to the correct cell reference.
Range is the name of the range you want to select from in listbox2, put the range you want in here.
G1 is an empty cell, you can use any cell that you know will always be empty.

You will get a message box when you click OK (it will say 'The Source currently evaluates to an error. Do you want to continue?), just click Yes.
This message will not appear again, it only appears the first time you enter the formula.

When you have a value in listbox1, listbox2 will be disabled, when listbox1 is empty, you will be able to select from a list in listbox2.

Hope this helps.
Ian

 

[Monty]

Hello Welshlan...Thank u so very much for u response....Could u please advise...if listboxes are on userform with multiple selection. If any value is selected in listbox1 then listbox2 should be disabled and If any value is selected in listbox2 then listbox1 should be disabled.....Thanks onces again...wating for ur reply.....Cheers

 

[WelshIan]

I haven't looked at forms in a few years, I'll have to look into it over the weekend and get back to you.

 

[WelshIan]

Hi Monty

Open the Visual Basic Editor, right click on your form and choose 'view code'.

Paste the following:

Private Sub ListBox1_Click()

Dim lCount As Long
Dim Listbox1Count As Long
' check if anything is selected in ListBox1
For lCount = 0 To Me!ListBox1.ListCount - 1
    If Me!ListBox1.Selected(lCount) Then
        Listbox1Count = Listbox1Count + 1
    End If
Next lCount
    
' Depending on whether something is selected in the listbox, 
' clear listbox2 or leave the range.

If Listbox1Count >= 1 Then
    With Me!ListBox2
            .RowSource = "Blank"
    End With
Else
    With Me!ListBox2
            .RowSource = "veg"
    End With
End If
End Sub


Private Sub ListBox2_Click()

Dim lCount As Long
Dim Listbox2Count As Long


For lCount = 0 To Me!ListBox2.ListCount - 1
    If Me!ListBox1.Selected(lCount) Then
        Listbox2Count = Listbox2Count + 1
    End If
Next lCount
    
          
If Listbox2Count >= 1 Then
    With Me!ListBox1
            .RowSource = "Blank"
    End With
Else
    With UserForm1.ListBox1
            .RowSource = "fruit"
    End With
End If
End Sub

There are 2 macros, one runs when the user selects an item from listbox1, the other with listbox2.

I have used 3 named ranges for the lists in the 2 listboxes- fruit (listbox1), veg (listbox2) and Blank. These will need to be changed to whatever your ranges are. Blank is a named range with a blank cell as a range, as in my earlier reply.

Depending on what you want your form to do, I would also add a button with the code below that clears the selections in your ranges. I would also set the 2 ranges when the form is opened.

With UserForm1.ListBox1 'use your form name
            .RowSource = "fruit"
    End With
With UserForm1.ListBox2
            .RowSource = "veg"
    End With

On a brief test it worked for me but let me know if you have any problems.

Thanks
Ian