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

--
Message posted from http://www.ExcelForum.com

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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"dmthomas " wrote in message
...
I am trying to run a macro that will add a line directly above the
button calling the macro but because the button doesn't actually sit in
a cell, it sits on the worksheet, it doesn't know where to add the line
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 seem
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 open
specified file." (Add_New_Line() is my sub and Add Next Line is my
text.)

Any help would be much appreciated.

Thanks


---
Message posted from http://www.ExcelForum.com/

Dick,

Thanks that worked a treat :-)

Danielle


---
Message posted from http://www.ExcelForum.com/