Listbox to select range on sheet

[Jimmypop]

Hi All

Me again...

I have searched but nothing seems to work or the code is so complex I cannot seem to adapt to my needs.

I have a list box on a userform which is populated from a range on the worksheet.

Now when I click on an item in the listbox I just want to select a certain range on the worksheet. Each listbox entry will have a different range that gets selected...

 

[rlv01]

If the ListBox data is in the form of a range address:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.ListBox1
        With ActiveSheet.Range(.List(.ListIndex))
            .Select
        End With
    End With
End Sub

 

[Jimmypop]

If the ListBox data is in the form of a range address:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.ListBox1
        With ActiveSheet.Range(.List(.ListIndex))
            .Select
        End With
    End With
End Sub

Getting a Runtime error 1004 on line

Private Sub ListBox1_Click()
    With Me.ListBox1
        With ActiveSheet.Range(.List(.ListIndex)) 'getting error here'
            .Select
        End With
    End With
End Sub

So basically if I select an item in listbox e.g. "Head Office" then the code needs to select the range "A14:A28" and if I select "Training Centre" then it needs to select range "A30:A37" etc. the listbox is located on a userform and gets populated from range "O13:O21"... ranges to select does not change so I am guessing would need to work with "Select Case" or similar... and I realise I use Click and not Double click but even on double click I get same error...

 

[rlv01]

As I noted in my reply, the code assumes that the list entry is just the range address, i.e. "A1" or "A14:A28". It makes that assumption because you did not mention in your original post that it did something else. If double clicking on a list item produces a result "Head Office" , then you need to have a way to relate "Head Office" to a range address that can then be selected.

 

[Jimmypop]

Got it working as wanted. Thanks for assistance. I eventually had to use Case and Select Case.