Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Tooltip And Macro On A Shape In Excel, Vba

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am trying to attach a tooltip (through hyperlink route) and a macro to an existing shape. The code is something like this -


Sub testtooltip()
    Dim myDocument As Worksheet
    Dim shp As Shape
    Dim strTooltip As String, strMacroName As String
    Set myDocument = Sheets("MyDashboard")
    strTooltip = "setting this tooltip - "
    strMacroName = "'" & ActiveWorkbook.Name & "'" & "!" & "RefreshDashboard"
    With myDocument
        Set shp = .Shapes("shp_button_refresh")
        .Hyperlinks.Add Anchor:=shp, Address:="", ScreenTip:=strTooltip
       shp.OnAction = strMacroName
    End With

End Sub

I am calling this Sub on Workbook_Open. As I see, the tooltip gets assigned to the shape without any problem and also, the Macro name too seems to get assigned. BUT, on click of the shape, nothing happens, meaning, the assigned macro never gets called. If I comment out the tooltip assigning line, then macro gets called!!!

I did see similar macro not getting called problem being posted in a few places, but none of them seemed to provide a proper answer Help please?

Note: My Excel version is 2007.
P.S: I had submitted this as a response to another old thread. Posting it in a new thread realizing that the old one may not surface at all...

View Answers     

Similar Excel Tutorials

How to Add Boxes, Buttons, Arrows, Speech-Bubbles, Hearts, and More to a Spreadsheet in Excel
In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as well as adding text to t ...
Quickly Add, Edit, & Manage Business Specific Diagrams in Excel (SmartArt)
In this tutorial I am going to show you how to add, edit, and manage Business oriented diagrams, charts, and shapes ...
Format Any Element of a Chart in Excel
In this tutorial I am going to go through the Format tab in more detail and show you how to Format every element of ...
Prevent Images and Shapes from Resizing or Moving in Excel
How to stop Images and Shapes from resizing in Excel when you change the size of rows and columns. This will allow ...

Helpful Excel Macros

Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da

Similar Topics

Hi Excel enthustiasts,

I would like to have a button or shape in Excel showing a screentip (or similiar) without using a hyperlink (or having the hyperlink disabled). I tried to use both button and hyperlink, but the hyperlink has a higher priority than the macro assigned to the button or the shape. I want to use the hyperlink-Screentip function, but not the hyperlink and the button-assigned macro. It does not seem that MouseOver works with shapes or buttons. (I actually prefer to use a shape rather than a button, as I then can have it transparent.)

Any ideas?

I realixe my post is not clear.
What i want:
A shape connected to a macro, so it works like a button. I click and something happens. The macro itself is not the difficulty.
I also want to have some explaining text for the shape when I drag my mouse over. This can be compared with the ScreenTip tool for a hyperlink.

I hope this is clearer...
I appreciate any good ideas :-)

I'm using EXCEL2007
I want to create a macro that will place an already formatted shape in a cell. Below is the macro that will do this. I'm trying to add code to make the border of the shape a particular style. I've tried: .Line.LineColor.RGB = RGB(217,217,217) after .LineDashStyle = masLineSolid which doesn't work. The name of the shape (OVAL) comes from the tabs: INSERT and SHAPE. The numbers 150 and 450 are where the shape is to go on the sheet. The numbers 59 and 17 is for the size. the RGB numbers are the color for the fill. msoLineSolid is for the type of line for the border. I'm tring to get the border to be a light gray (217,217,217)

Sub oval()
Set myDocument = Worksheets(1)
With myDocument.Shapes.AddShape(msoShapeOval, _
150, 450, 59, 17)
.Name = ""
.Fill.ForeColor.RGB = RGB(147, 205, 221)
.Line.DashStyle = msoLineSolid
End With
End Sub


I am trying to create a table of contents for a very long list of items on a sheet. I am using shapes on a seperate sheet to act as buttons, that go to the location on the correct sheet using a hyperlink. I found how to add a hyperlink using a macro, but I can't find how to create a hyperlink for the shape using a macro.

What is the code I would need to set a hyperlink to "Sheet1!A2:Sheet1!C2", with the a tooltip of "Some text"?

Here is how I create the shapes:

With myDocument.Shapes.AddShape(msoShapeRoundedRectangle, 10, IntVerticalOffset, 630, IntHeight) 
    .Fill.ForeColor.RGB = RGB(204, 255, 204)
    .Line.ForeColor.RGB = RGB(0, 0, 0)
    .TextFrame.Characters.Text = StrRowName
    .TextFrame.HorizontalAlignment = xlHAlignLeft
    .TextFrame.Characters.Font.ColorIndex = xlAutomatic
    .TextFrame.Characters.Font.FontStyle = "Bold"
    .Locked = True
End With

Hello all,

I've got a macro that I can rund when I click on a shape. It's called "Shape_Click".

Basically, I want this macro to be automatically assigned to any shape I create, as I create it.

I know the line will be something like

Please Login or Register  to view this content.

But how do I name the sub to get it so this macro runs when a new shape is created?


Is there any way that I can get the name of the shape i just clicked to call a procedure?

I have 35 shapes in my Worksheet.
Each shape has a different name. Each shape has a macro procedure assigned, wich do almost the same procedure (with just small differences, depending on from wich shape the macro was called).

I want to make just one macro procedure and catch from wich shape the macro was called.

I have a shape with instructions to enable macros when opening an excel document. Once users have enabled the macros I would like them to select a shape with a macro assigned to it that says "complete" and it will delete the instructions and the "complete button/shape." I tried recording a macro to get started but when I record a macro nothing is written in the VBA. Is there a code I can write in a new macro assigned to a shape/button to delete shapes?

I have a macro assigned to a text box shape called "Detail" (With this is clicked by the user, the macro is invoked). I want to copy this shape to Column A for all rows with data in the worksheet. The code I have below copies the shape, which is in "A1", reads each row in the worsheet, and pastes the shape to cell "An" for each row. The problem is that with over 5000 rows, it takes a long time to run. My workbook is a query tool used by many users, so they have to sit and wait for this to step complete. Is there a more efficient way to do this. Can shapes be created in the worksheet in a more time efficient way ?

Please Login or Register  to view this content.

Any help appreciated.

Moderator Note:

Pls use code tags around your code next time as per forum rules.

Hi All,

Hope all is well.

I'm trying to write a macro in Powerpoint'07 and I am very new in writing macros in ppt'07.

I have created a commandbutton in slide1 and if I click this I will get one(or)more shapes (The shape is arrow ---->). This is done by using the below code.

Set myDocument = ActivePresentation.Slides(1)
With myDocument.Shapes.AddLine(BeginX:=350, BeginY:=120, EndX:=450, EndY:=230).Line
'.DashStyle = msoLineDashDotDot
.BeginArrowheadStyle = msoArrowheadNone
.EndArrowheadStyle = msoArrowheadOpen
.EndArrowheadWidth = msoArrowheadWidthMedium
.EndArrowheadLength = msoArrowheadLengthMedium
.ForeColor.RGB = RGB(251, 0, 0)
End With

Now, I have created another commandbutton in slide1 and I need code to clear all the shapes (The shape is arrow ---->) by clicking this button.

Expecting your reply.


I have a shape called Oval1.

I would like to set things up so that if a user points the cursor over the shape, then a macro will be activated.

I know how to set things up to activate if the user clicks on the shape:

Sub Oval1_Click()
Do my stuff
End Sub

However, in this new case, I do not want them to have to click - just pass over the shape.

Could someone give me the basic macro-form? Is it a macro associated with the worksheet, as opposed to the shape itself?



Hi all,

I have several workbooks that each have about 20 sheets, and each sheet has dozens of shapes (text boxes, command buttons, ovals, etc.) and each shape has a macro assigned to it. What I want to do is disable the OnAction property from each shape with one macro.

I have been able to do them individually by selecting each shape and setting OnAction = "", but when I try to do set this up for an entire worksheet or workbook I'm getting the message "Unable to set the OnAction property of the DrawingObjects class." Below is the code that I've tried.

This sub works if I do them one by one:

Sub DisableShapes1 ()
ActiveSheet.Shapes("Text Box 1").Select
Selection.OnAction = ""
End Sub

But this sub gives me the error:

Sub DisableShapes2 ()
Selection.OnAction = ""
End Sub

If I run the 2nd Sub on a sheet that does not have any command buttons it seems to works fine. How do I go about getting this to work? Would I need to instead try to loop through each shape?

Any help appreciated...


Clicking a shape with no macro assigned selects the shape. Clicking a shape that has a deleted macro assigned produces an Excel error message "The macor 'macro name' cannot be found."

How do you entirely remove a macro assignment for a shape? Deleting the macro code doesn't remove the assignment. Going through the Assign Macro process and deleting the macro name from the Macro Name field doesn't work.

Hi friends,

I have given hyperlink for an image, when I move cursor on image it is showing tooltip (path of the file). Please kindly reply how can we remove tooltip to hyperlink.

cross posted at

Thanks & Regards

Is it possible to do what is in essence a find and replace on tooltips in a VBA Project that contains many user forms.
ie. several tooltips refer to say 2010 and I want to change it to 2011 ?
Taking this one stage further, would it be possible to do multiple changes in one loop
I'm thinking along the lines of
with forms
form1, form2, form3 etc
if tooltip.value = 2010 then tooltip.value = 2010
if tooltip.value = "Enter Month Due" then tooltip.value = "Enter Date Due"

Any ideas , or is it a non starter ?.
Thanks for any help

Hi friends,

I have given hyperlink for an image, when I move cursor on image it is showing tooltip (path of the file). Please kindly reply how can we remove tooltip to hyperlink.

Thanks & Regards


I have assigned a macro to a picture button in XL 2007. Is it possible to have a tooltip via VBA for the button?

Thanks in advance


I've run into a small problem with shapes..

I have an excel workbook that copies rows from one sheet to put into another sheet. The originating rows have 1 shape attached. On click of this shape, i want to to identify the row it is on, and delete it.

The Problem:
I've found no way to identify the row the shape is on, or even the name of the shape. On click of the shape, it runs a macro. Since the image can be replicated 50 or more times, i have linked the original to 1 macro, which means all subsiquent copies of the shape, calls the same macro - hense the need to identify the row.

What i want, is to be able to identify the row that the shape is on, so i can identify which row to delete.

Any ideas??

I posted similar yesterday. No one helped...sniff sniff (where's the crying smily)

Now verified to be occur on other machines running various versions of Office 2007...

In Excel 2007, write this simple macro and assign it to a shape:


Private Sub CloseThis()
End Sub

My experience: Selecting shape to run macro crashes Excel. But if I just run it from the Macros dialog box or from the VB Module, it does not crash.

Is there something else special I need to do in 2007 to assign a Macro to a shape???

Do you have the same experience?



I have some command buttons placed onto an excel worksheet. Is it possible to assign tooltip (e.g. "Use this button to insert orders") to these objects? I do not see tooltip property.

any help would be much appreciated

Hi All
I'm building a wiring loom generator.
Macro/vba coding lets me add a shape(boxes etc) and add connectors.
If you click on the shape, the autogenerated name appears in the namebox.

How can I retrieve name into the code.

I assumed a ?????? .Name would be available but having tried all the combinations of etc I've hit a wall.

The value in the Name box would do but again , surfed all the net without success.

This code will generate your boxes and connect them together and then put the names in the sheet.

If you click on one of the boxes the name comes up in the namebox.

How can I refer to that value?

Sub boxes()
Set myDocument = ActiveSheet
Set s = myDocument.Shapes
Set firstrect = s.AddShape(msoShapeRectangle, _
100, 50, 200, 100)
Set secondrect = s.AddShape(msoShapeRectangle, _
300, 300, 200, 100)
Set newconnector = s.AddConnector(msoConnectorCurve, _
0, 0, 100, 100)
With newconnector.ConnectorFormat
.BeginConnect firstrect, 1
.EndConnect secondrect, 1
End With
Cells(1, 1).Value = firstrect.Name
Cells(1, 2).Value = secondrect.Name
Cells(1, 3).Value = newconnector.Name
end sub

Any ideas would be appreciated

Ok, this is an weird VBA question. I had originally wanted to display tooltips for individual list box items. Evidentially, that isn't possible. So I settled for a work around. A single click loads a customized tool tip, a double click will launch a macro associated with the list box value. The problem is that after the tool tip is loaded, the user has to move the arrow over a different object and then back again to see the altered tooltip text. Is there any way to force the display of a tooltip after it is changed?...or maybe some other way of doing this I am too stupid to think of


Private Sub DocumentLauncher_ItemsListBox_Click()
    ' changing the tool tip to the document description
    DocumentLauncher_ItemsListBox.ControlTipText = RetreiveDocumentDescription(GetOptionNumber("Launch"))
End Sub

is it possible to create a pop-up tootip (or show another shape or textbox with help text) when hovering a button (created from a shape)

When I refer to a shapes .onaction property, I expect to get the name of the macro I assigned to the shape. This may be to a macro in another workbook. This works fine in Excel 2003. However, in Excel 2007, when I refer to shape.onaction, I get "[0]!macroname" if the macro is in the activebook and "[1]!macroname" if the macro is in another book. Why do I get "[0]" or "[1]" instead of the path and workbook name in Excel 2007?


Dear All,

I have total eight shapes in my workbook. I want to assisg a macro for each shape and short cut key for each
individual shape.

While pressing the shortcut key i want be appear the corresponding shape.
suppose the Key (ALT+v ) the corresponding shape be appear where cursor of excel workbook blink.

Please assist,



I have been trying to add a hyperlink to a shape using the following in
Excel 2002:

ActiveSheet.Shapes("Object 1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1),
Address:= _

This code was generated by recording a macro. The hyperlink was successfully
added during the recording, and works just fine.

However, when I run this recorded macro, I get a:

Run-time error '5':
Invalid procedure call or argument

After a little experimentation, I found I can add a Range object as an
Anchor, but not a Shape object as the documentation indicates that I should.

Does anyone have a solution to this problem?




I'm trying to code for a button, such that when the user clicks it, the image (screenshot) they have on their clipboard will be pasted and re-sized. I've considered using a button with the following code:

Private Sub CommandButton1_Click()
ActiveSheet.Paste Destination: =Worksheets("Sheet1").Range("D1")
Set myDocument = Worksheets(1)
myDocument.Shapes(1).Width = 400
End Sub

The problem is that this only works if the shape is shape #1, which if the sheet is being re-used, or if they first pasted the wrong thing, etc, it wouldn't be. Any suggestions would be most appreciated! Thank you!