Linking multi-select ListBox to Cells Value

emilemil22

New Member
Joined
Aug 18, 2019
Messages
27
[h=2]Linking multi-select ListBox to Cells Value[/h]
Hello,

I have a multi-select Listbox1 with 4 valuees:
Monday
Tuesday
Wednesday
Thursday

I want to link it to a cell such that depending on the selections a column of YES or NO values is produced.
For example, if user selects only Monday and Wednesday then the following is produced in a different worksheet

YES
NO
YES
NO


Can anyone help with Macro?\

Thank you!​
 

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)
When the listbox is in multiselect, then the click event does not work.
You can use select values ​​and press a button

In that case use this:
Code:
Private Sub CommandButton1_Click()
  Range("A2").Value = IIf(ListBox1.Selected(0), "YES", "NO")
  Range("A3").Value = IIf(ListBox1.Selected(1), "YES", "NO")
  Range("A4").Value = IIf(ListBox1.Selected(2), "YES", "NO")
  Range("A5").Value = IIf(ListBox1.Selected(3), "YES", "NO")
End Sub


Or press double click and then it will be automatic:
Code:
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Range("A2").Value = IIf(ListBox1.Selected(0), "YES", "NO")
  Range("A3").Value = IIf(ListBox1.Selected(1), "YES", "NO")
  Range("A4").Value = IIf(ListBox1.Selected(2), "YES", "NO")
  Range("A5").Value = IIf(ListBox1.Selected(3), "YES", "NO")
End Sub


4f2738999957db8b656a669d98572ee8.jpg
 
Upvote 0
When the listbox is in multiselect, then the click event does not work.
@Dante, my Mac doesn't support ActiveX on worksheets, but with a mulit-select userform listbox, the Click event doesn't fire, but the Change event does.
Do worksheet ActiveX listboxes also do this?
 
Upvote 0
@Dante, my Mac doesn't support ActiveX on worksheets, but with a mulit-select userform listbox, the Click event doesn't fire, but the Change event does.
Do worksheet ActiveX listboxes also do this?

Hi @mike

You're right, the change event works. I thought about it, but I didn't try it, because I wasn't modifying the lisbox, just selecting :oops:. Thanks for the information.

With ActiveX listboxes the same thing happens, the click event does not fire, but the change event does.


Hi @emilemil22



You can use the following:


Code:
Private Sub ListBox1_Change()
  Range("A2").Value = IIf(ListBox1.Selected(0), "YES", "NO")
  Range("A3").Value = IIf(ListBox1.Selected(1), "YES", "NO")
  Range("A4").Value = IIf(ListBox1.Selected(2), "YES", "NO")
  Range("A5").Value = IIf(ListBox1.Selected(3), "YES", "NO")
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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