Linking multi-select ListBox to Cells Value
Results 1 to 6 of 6

Thread: Linking multi-select ListBox to Cells Value

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Linking multi-select ListBox to Cells Value

    Linking multi-select ListBox to Cells Value

    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!

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,767
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Linking multi-select ListBox to Cells Value

    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

    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Linking multi-select ListBox to Cells Value

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

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,767
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Linking multi-select ListBox to Cells Value

    Quote Originally Posted by emilemil22 View Post
    @Dante Amor that was exactly what I needed!
    Thank you very much!
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  5. #5
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,521
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Linking multi-select ListBox to Cells Value

    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?

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,767
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Linking multi-select ListBox to Cells Value

    Quote Originally Posted by mikerickson View Post
    @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:


    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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •