Assign a Hyperlink to a button

[Mike K]

Oh Wise Ones,
I have been searching old posts and cannot find
something that works for me. I would like to assign a hyperlink to another
sheet to a button. It was suggested to use the Follow Hyperlink event, but
it does not show up as an assignable macro. The command button click event
would not allow me to get to click mode on the button- always had crosshairs.
Obviously I'm doing something wrong, and some help would be much
appreciated.

1) Place a button on the worksheet.
2) click on it
3) takes me to:
HYPERLINK("file:S:\common\Lab\DataSheets\Asphalt\Filler\Filler%
Datasheet.xls" (not sure if the syntax is right, its what I use in a
cell)

Thanks,
Mike

 

[Dave Peterson]

If you used a commandbutton from the control toolbox toolbar, you don't assign a
macro to it. It has its own _click event.

If you go back to that button and double click on it (make sure you're in design
mode--another icon on that control toolbox toolbar), you'll see a code windo
open.

You can use a macro like:

Option Explicit
Private Sub CommandButton1_Click()
ThisWorkbook.FollowHyperlink _
Address:="File:\\\\S:\common\Lab\DataSheets\Asphalt\Filler\Filler%" _
& "Datasheet.xls"
End Sub

You could also use a shape from the drawing toolbar (or a insert a picture) and
directly assign the hyperlink to that shape/picture.

 

[Gary''s Student]

Put ANY piece of ClipArt on the worksheet. (if you like buttons, use a
rectangle from AutoShapes)

Right-click the Object and pick Hyperlink from the resulting menu.

 

[Mike K]

No problems when assigning the hyperlink, but when I click it I get an error
"Cannot open specified file"

 

[Mike K]

Dave,
Getting and error "Compile error, variable not defined" when
running.

Mike

 

[Dave Peterson]

You changed something and didn't share what you did.

I don't have a guess how you changed it.

 

[Dave Peterson]

You changed something and didn't share what you did.

I don't have a guess how you changed it.

 

[Dave Peterson]

I'd guess you don't have a file with that filename in that path.

 

[Mike K]

Dave,
Thanks for your time and effort. I already use that file in a
regular hyperlink in a cell. Nothing has been changed. I copy/pasted your
whole macro. At this point I can use workbook_open on a button(I recorded it
and it worked) , I was just trying to use the hyperlink in a macro to see
how it worked.

In summery:
this function IN A CELL works:
=HYPERLINK("file:S:\common\Lab\DataSheets\Asphalt\Filler\Filler%
Datasheet.xls","Filler %")

The fonts on my cell hyperlinks tend to get "adjusted" from time to time. I
thought buttons would work better when other people use them.


Thanks,
Mike

 

[Dave Peterson]

Make sure I didn't make a typo.

Make sure that the file exists in that location.

I tried it again and got a "cannot open file" prompt. I don't have that file
available.

Dave,
Thanks for your time and effort. I already use that file in a
regular hyperlink in a cell. Nothing has been changed. I copy/pasted your
whole macro. At this point I can use workbook_open on a button(I recorded it
and it worked) , I was just trying to use the hyperlink in a macro to see
how it worked.

In summery:
this function IN A CELL works:
=HYPERLINK("file:S:\common\Lab\DataSheets\Asphalt\Filler\Filler%
Datasheet.xls","Filler %")

The fonts on my cell hyperlinks tend to get "adjusted" from time to time. I
thought buttons would work better when other people use them.

Thanks,
Mike