selected cells in Listbox get selected on spreadsheet

Posted by Gernot on May 23, 2001 7:29 AM

Hej folks,

i have a listbox with multiple selection which list the content of a cell range a1..a200. Some of them will be selected in the userform and I want to have the respecting cells selected on the spreadsheet as well.

Thanks

Posted by mseyf on May 23, 2001 1:20 PM

I'm still a VBA amateur, but I tried to cobble something together for you. Basically, this routine loops through the entire row source to determine which items were selected and creates a string so the items can be selected through a range array. Hope you can glean some usefull info from this mess.

Mark

Private Sub cmdSelected_Click()

Dim strCellsSelected As String 'string to accumulate cell addresses
Dim intRelativePosition As Integer 'row number of selected item
Dim intListSize As Integer '# of items in list
Dim X As Integer
'in this example, the list box is named "lbxRow"

strCellsSelected = ""
intListSize = frmTest.lbxRow.ListCount

For X = 0 To intListSize - 1
If lbxRow.Selected(X) Then
intRelativePosition = WorksheetFunction.Match(lbxRow.List(X, 0), Range("a:a"))
strCellsSelected = strCellsSelected & ",a" & intRelativePosition 'create array for range
End If
Next

strCellsSelected = Right(strCellsSelected, Len(strCellsSelected) - 1) 'get rid of leading comma
Range(strCellsSelected).Select

End Sub