Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Feb 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What is the macro code for selecting an item (say item 2) in a list box?
    i.e. something along the line? - Sheets(1).Shapes("List Box 9").list(2).select

    (I want to show what item on the list box was selected based on input from another spreadsheet)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If the list box is linked to a cell, you can set the value of that cell. The corresponding item in the list box will be selected automatically.


  3. #3
    Lounger
    Join Date
    Feb 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I looks like cell links work for single type list boxes (thanks) but not mult type list boxes; is there a way for them?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Is it a list box from the Forms toolbar or from the Control Toolbox toolbar?

    What do you want to do if one or more items have already been selected? Do you want to add the item to the selected items, or should it replace the already selected items?


  5. #5
    Lounger
    Join Date
    Feb 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It is a forms control multi-list box

    I have one mult-list box on sheet(1) and one mult-list box on sheet(2) - both mult-list boxes are linked to the same macro.
    Whatever multi-list box items the user selects on sheet(1); I want reflected on the sheet(2) multi-list box. And vise versa.
    (This way the user know what exactly was selected when jumping between spreadsheets)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Assign a macro like this to the first list box. You must, of course, substitute the correct names.

    Code:
    Sub SetListBox()
     Dim shp1 As ListBox
     Dim shp2 As ListBox
     Dim i As Long
     Set shp1 = Worksheets("Sheet1").ListBoxes("List Box 1")
     Set shp2 = Worksheets("Sheet2").ListBoxes("List Box 1")
     For i = 1 To shp1.ListCount
      shp2.Selected(i) = shp1.Selected(i)
     Next i
    End Sub

  7. #7
    Lounger
    Join Date
    Feb 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Assign a macro like this to the first list box. You must, of course, substitute the correct names.

    Code:
    Sub SetListBox()
     Dim shp1 As ListBox
     Dim shp2 As ListBox
     Dim i As Long
     Set shp1 = Worksheets("Sheet1").ListBoxes("List Box 1")
     Set shp2 = Worksheets("Sheet2").ListBoxes("List Box 1")
     For i = 1 To shp1.ListCount
      shp2.Selected(i) = shp1.Selected(i)
     Next i
    End Sub

  8. #8
    Lounger
    Join Date
    Feb 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks

Posting Permissions

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