list box, combo box hyperlinks

[P-C-Surgeon]

Can a validation cell listbox or the control toolbox combo box run
macro to have the selections link to websites or documents on my har
drive when they are clicked on? If so, does anybody know the code

 

[jeff]

Hi,

Yes, it can be done. Try this code - it assumes your
Listbox is linked to cell E1:

Private Sub ListBox1_Click()
Dim cc As Variant
Range("E1").Select
If Left(Selection, 5) = "http:" Then
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=Selection
Else
cc = Selection
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:="", SubAddress:=cc

End If
testme
End Sub

Sub testme()
With Selection(1)
If .Hyperlinks.Count > 0 Then
.Hyperlinks(1).Follow NewWindow:=True,
addhistory:=True
Else
If .Formula Like "=HYPERLINK(*" Then
ActiveWorkbook.FollowHyperlink .Value, _
NewWindow:=True, addhistory:=True
End If
End If
End With
End Sub
jeff

 

[jeff]

P-C:

The code is a bit heavy because it adds whatever you
have put into the ListFillRange as true hyperlinks, so you
can just key in ''Sheet3'!C6 (with all the apostrophes)
and it'll be turned into a hyperlink used in Cell E1.

If you are adding yoiur linked cells as true hyperlinks,
then you can modify the ...Add code (delete it).

In this case, my linked cell was E1, my ListFillRange
was H1:H5 containing a link to Microsoft and other loca-
tions on other sheets.

the Listbox1 is from the toolbox, not from Forms.

What error are you getting?

Sorry it doesn't seem to suit your taste or needs.
jeff