Get Range from Listbox selected items

[sharky12345]

I'm using this to populate a Listbox on a Userform;


Me.ListBox1.List = Sheets("Sheet1").Range("A5", Range("A100000").End(xlUp)).Value

Is there a way I can identify what Range the selected items refer to?

The range my not be contiguous as far as the row but will be in the same column and the Listbox is multiselect.

Posted elsewhere too;

http://www.mrexcel.com/forum/excel-questions/880621-get-range-listbox-selected-items.html

[mancubus]

did MickG's code at MrExcel work for you?

[mikerickson]

Something like this should work.

Private Sub CommandButton1_Click()
If SelectedRange Is Nothing Then
MsgBox "no selection"
Else
MsgBox SelectedRange.Address & " chosen from listbox"
End If
End Sub


Private Sub UserForm_Initialize()
ListBox1.List = DataRange.Value
ListBox1.MultiSelect = fmMultiSelectMulti
End Sub

Function SelectedRange() As Range
Dim dRange As Range
Dim i As Long
Set dRange = DataRange
Set SelectedRange = dRange.Offset(dRange.Rows.Count + 3, 0)
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
Set SelectedRange = Application.Union(SelectedRange, dRange.Cells(i + 1, 1))
End If
Next i
End With
Set SelectedRange = Application.Intersect(SelectedRange, dRange)
End Function

Function DataRange() As Range
With Sheet1.Range("A:A")
Set DataRange = Range(.Cells(5, 1), .Cells(1000000, 1).End(xlUp))
End With
End Function