excel2000
i'm using a cell with a hyperlink to a website and 'calllng' it with a
command button,
Private Sub CommandButton3_Click()
Range("o17").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
ActiveWindow.WindowState = xlNormal
End Sub
i change the value of the website url based on the value entered in another
cell
Range("O17").Select
Selection.Hyperlinks(1).Address =
"http://www.ivolatility.com/options.j?ticker=" &
ActiveSheet.Range("stock_symbol").Value & ":NYSE&R=1&period=24&chart=2&vct="

this works ok but i would prefer not to have the cell containing the
hyperlink not visible to the user
but the only way i have found to implement the hyperlink is first 'insert'
the hyperlink into a cell, then change the value of the hyperlink address
programmatically, this always leaves the original entered url visible for
that cell
is there some other way to have a hyperlink, whose address i can change in
vba, that is not visible to the user??

just use a hyperlink that points to itself

then trap FollowHyperlink event

ie:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Select Case Target.Range
Case [b3]
ActiveWorkbook.FollowHyperlink "http://www.microsoft.com"
End Select
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"bbxrider" wrote:

excel2000
i'm using a cell with a hyperlink to a website and 'calllng' it with a
command button,
Private Sub CommandButton3_Click()
Range("o17").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
ActiveWindow.WindowState = xlNormal
End Sub
i change the value of the website url based on the value entered in
another cell
Range("O17").Select
Selection.Hyperlinks(1).Address =
"http://www.ivolatility.com/options.j?ticker=" &
ActiveSheet.Range("stock_symbol").Value &
":NYSE&R=1&period=24&chart=2&vct="

this works ok but i would prefer not to have the cell containing the
hyperlink not visible to the user
but the only way i have found to implement the hyperlink is first
'insert' the hyperlink into a cell, then change the value of the
hyperlink address programmatically, this always leaves the original
entered url visible for that cell
is there some other way to have a hyperlink, whose address i can
change in vba, that is not visible to the user??

hi thanx for the reply
not exactly sure how that works, do you set the link url address the
same way as i have been?

but will experiment
bbxrider

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

the trick:

the hyperlink in the sheet should point to itself,
it's just there to trigger the followhyperlink event.
where you can jump to anything you like without the user seeing it.


programmatically adding a selfpointing hl:

Sub ff()
Dim r As Range
Set r = [b3]
r = "ClickMe"
r.Hyperlinks.Add r, "", r.Address
End Sub

above code works in xl97 as it avoids the "TextToDisplay argument.
when coding for newer versions only, use:

Sub ff()
Dim r As Range
Set r = [b3]
r.Hyperlinks.Add r, "", r.Address,"ClickMe"
End Sub



My example FollowHyperlink handler in previous post must be (hard)coded
based on the hyperlink's anchor

However with a bit of ingenuity you could easily use the cell's contents
for a select case like

Select case Hyperlink.Range.Text
case "ClickMe"
etc


OR..
or use the commenttext in the same cell...
(hide the comment indicators in activesheet)

activeworkbook.followhyperlink hyperlink.range.comment.text


many ways to go to Rome...



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


robert betz wrote:


hi thanx for the reply
not exactly sure how that works, do you set the link url address the
same way as i have been?

but will experiment
bbxrider

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

thanks again for the help, am having trouble keeping up with newsgroup,
its only giving me the current day, so if i miss a reply then the only
back to it seems to be thru developersdex
bbxrider

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

dl a real newsreader like XNEWS (freeware & lots safer than OE)
then setup to server msnews.microsoft.com

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


robert betz wrote:


thanks again for the help, am having trouble keeping up with newsgroup,
its only giving me the current day, so if i miss a reply then the only
back to it seems to be thru developersdex
bbxrider

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!