listbox

[Greg B]

I have a listbox which I am listing cells a1:a1000 on a worksheet called
"homes". I would like the ability to have the user highlight the item in
the list box and execute a code that will go to that cell.

Is this possible

Thanks

Greg

 

[Tom Ogilvy]

Assuming a listbox from the control toolbox toolbar, you can use the click
event:

Private Sub ListBox1_Click()
If listbox1.ListIndex <> -1 then
Range(Listbox1.Value).Select
End If
End Sub

 

[Greg B]

Thanks Tom

Assuming a listbox from the control toolbox toolbar, you can use the click
event:

Private Sub ListBox1_Click()
If listbox1.ListIndex <> -1 then
Range(Listbox1.Value).Select
End If
End Sub

 

[Greg B]

Just a little further with this one. Am I able to execute a hyperlink using
this method? I have tried it but it comes with the runtime error.

Thanks
Greg

 

[Tom Ogilvy]

What do you mean by execute a hyperlink. You asked about selecting a cell.

You would have to describe what you want to do if you hope to get an answer
that is relevant. Just using code to select a cell containing a hyperlink
doesn't execute the hyperlink - just like you can select a cell manually and
not fire the hyperlink.

Private Sub ListBox1_Click()
Dim rng as Range
If listbox1.ListIndex <> -1 then
set rng = Range(Listbox1.Value)
if rng.hyperlinks.count > 0 then
rng.select
rng.parent.parent.FollowHyperlink rng.hyperlinks(1).Address, _
rng.Hyperlinks(1).SubAddress, True
end if
End If
End Sub

might be close - it depends on what you have actually populated the Listbox
with.