Running a macro from a hyperlink

  • Thread starter Thread starter dmthomas
  • Start date Start date
D

dmthomas

I am trying to run a macro that will add a line directly above th
button calling the macro but because the button doesn't actually sit i
a cell, it sits on the worksheet, it doesn't know where to add the lin
and so just adds it above the last selected cell, not above the button


I thought perhaps a hyperlink could call the macro but it doesn't see
to be able to. I have set up my hyperlink as follows:

=HYPERLINK("Add_New_Line()", "Add Next Line")

but when I click on the link it displays the error "cannot ope
specified file." (Add_New_Line() is my sub and Add Next Line is m
text.)

Any help would be much appreciated.

Thank
 
dmt

You can run a macro from a hyperlink by using the FollowHyperlink event, but
I wouldn't recommend it for this application. Check out Application.Caller
if you're using a commandbutton from the Forms toolbar. You can use a macro
like this to find the cell above the button.

Sub test()

Dim shp As Shape

Set shp = ActiveSheet.Shapes(Application.Caller)

shp.TopLeftCell.Offset(-1, 0).Value = _
"This is the line above the button"

End Sub

You may want to add some error checking if you can call your macro in other
ways.
 
Back
Top