Linking multi-select ListBox to Cells Value

[emilemil22]

[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!​

 

[DanteAmor]

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:

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:

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

 

[emilemil22]

@Dante Amor that was exactly what I needed!
Thank you very much!

 

[DanteAmor]

@Dante Amor that was exactly what I needed!
Thank you very much!

I'm glad to help you. Thanks for the feedback.

 

[mikerickson]

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?

 

[DanteAmor]

@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 . 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:

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