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 ...
Run a Macro When you Click a Button in Excel
Make a macro run when you click a button in the worksheet in Excel. This allows you to create a button and put it a ...
How to View, Arrange, or Hide All Shapes, Charts, Pictures, Etc. in Excel
In Excel, you can add shapes, images, objects, photos, and all sorts of items to the spreadsheet. The problem is th ...

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

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

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...


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


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 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

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)

I have searched the forums for an answer to this and haven't found anything, but I am forever hopeful!

Is there a way to trigger a macro when a shape is moved by the user? If I set the OnAction property for a shape, then the cursor changes to a hand when it is over the shape, and I can detect a mouse click on the shape, but I cannot drag and drop the shape. If I reset the OnAction property to "" (empty quotes), then I can drag and drop again within the worksheet, but I can't trigger the macro.

Worksheet_Change or Worksheet_SelectionChange are not triggered by this event.

People have suggested using timers to continuously poll the shapes and determine their locations, but is there an easier way?

What I am trying to do is create a sheet where the user can visually move around objects (in this case representing employees) and deposit them in various zones. The spreadsheet would then apply certain attributes to the shape i.e. change colour according to where the shape is and if it is in an unsuitable zone. I can do all of this, but I want it to work the instant the shape is moved, not rely on the user to hit a button.



I am using Excel 2007 and creating code that will run in Excel 2003.

Can someone help me with my code? It is not executing the OnAction macro.

This is my first time creating a shape that executes a macro and I am getting a popup message when I click on the shape that says, "Cannot run the macro Shapeclick1. The macro may not be available in this workbook or all macros may be disabled"

1. The macro is available in the same module as the code that created the shape.
2. Macros are not disabled because I created the shape successfully (minus the OnAction command working)

Here are the two subroutines that create the shape and then the macro it is supposed to run:

Public newbtn As Shape


Private Sub Create_232_ICT_TD_toPivot_Button()
    Sheets("232 Charts").Select
    Set newbtn = ActiveSheet.Shapes.AddShape _
        (msoShapeRectangle, 10, 10, 95.76, 18)
    newbtn.Fill.ForeColor.RGB = RGB(6, 56, 109)
    newbtn.Select          '  Selects the shape
    Selection.Cut          '  Cuts the shape
    ActiveSheet.Paste          'positions the shape in cell E2
    With Selection
        .Caption = "View Pivot Table"
        .Font.Color = RGB(255, 255, 255)
        .Font.FontStyle = "Bold"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .OnAction = "ShapeClick1"
    End With
End Sub

The code above successfully creates the shape, but the .OnAction is not working.


Private Sub ShapeClick1()
    Sheets("Top Defects Pivot").Select          
    ActiveSheet.PivotTables("Top_Defects").PivotFields _
        ("Assembly(s)").CurrentPage = "ARCT00232"    'Changes the PT Assembly #
    ActiveSheet.PivotTables("Top_Defects") _
        .PivotFields("Proc").CurrentPage = "IF1"     ' Changes the PT dept.

A note on the private subs: I like the subroutines to be private so users cannot see them on the macro list. Public variables work in other code I have written. I tried removing the private from the Shapeclick1 sub and still receive the same error after recreating the shape.

Thank you in advance for your help.

I have created my own function in Excel. But, now if someone use that formula in the sheet. I need to get the tooltip which we generally get with formulas like ex : =Average( (we get a yellow tooltip ). How do i make that appear for User Defined Functions.
Please help.

Hi I've inserted a basic shape and assigned a macro to it on my worksheet. I can't work out how to unprotect this shape (hence enabling the use of the macro button) whilst keeping the rest of the worksheet protected.

Thanks in advance for your help!


Attached is the code I use to add a shape to a temp worksheet, can anyone please advise me how to add a 3d bevel effect to it? In particular I wanted to use the shape effect bevel called 'Cool Slant'?

Or point me in the direction of another thread on this subject, as I couldn't find anything.

Please Login or Register  to view this content.

Edit: Im using Excel 2007 ifit makes any difference...


I'm stuck and need some help. I need to change the tooltip of a cell dynamically based on the contents and I'm struggling with this. I'll give you a example
I've 2 Sheets- Sheet A and Sheet B
Sheet A has a column Product with a dropdown
Sheet B has more details of the products like
A001 - Brakes
A002 - Headlights
A003 - Wipers

If the user selects or hovers on A001 in the dropdown the tool tip should display Brakes.
(It is fine if the tooltip is displayed after the selection is made as long as it changes dynamically based on the contents)

Is this too much to ask from excel?

I know how to set static tooltip using the Data Validation, but this is not something that will help me for obvious reasons.


HI all, I'm trying to figure out if it is possible to add a shape to a specific cell ie: rectangle and then assign a macro to the newly created shape? The reason I want to do this is because I am importing several rows of data and while the data imports, I want a rectangle shape to appear for each row and it will be a 'Modify' button. So I want to assign a macro to the shape so when a user clicks on the shape it will pop up a userform. But I want to do all of this with VBA since all of the data is being imported via VBA.

Hi Guys

I know how to set the tooltip using the UI editor but is there any way of turning off the annoying extra bit at the bottom that gives the file name and tells you to press F1 for help??




I am currently using the following macro to delete all shapes in a range on my worksheets. However, recently I happen to run into error 1004 telling me that an object is missing... Interestingly enough, when I end the complete macro (of which this one is a part of, basically a whole bunch of individual macros all called one after another) and then call this specific macro by itself, it seems to work with no problem.


Sub DeleteAllPics()

Dim ws As Worksheet
    For Each ws In Worksheets
            With ws
               Set rngCheck = .Range("A3:A50")
               For Each Shp In .Shapes
                  ' check if shape is located in our range
                  If Not Intersect(Shp.TopLeftCell, rngCheck) Is Nothing Then
                  End If
               Next Shp
            End With
        Next ws

End Sub

Well, since I cannot figure out what is going on, I would like to modify the macro above to firstly only run if the sheet name is either "VIP" - "PCP" - "LSG" or "Company", and secondly check if there actually IS any shape in the specified range before trying to delete it.

Since I did not write this code, and I am not familiar with shapes in vba, I was wondering if someone could give me a hand on this?
Thanks a lot!


I've written some code that places a large number of shapes on a worksheet. The code also names each shape. I'd like some code to show me how to scroll the window to a specific shape. I can easily get the top and left of the shapes location. But, how do I use that data to scroll to? is there a way to ust use the shape name?


I am using excel 2007, I created a shape from Excel (Insert-Shapes). I want this shape to appear and disappear base on some values but as a first step, I cant even get the visible property to work.

I created a button and in the click event, i do this code.

With ActiveSheet.Shapes("UpArrow3")
.Visible = Not .Visible
End With

I got an error that says....

"The item with the specified name wasn't found." - highlighted the 1st line.

Seems like excel cannot find UpArrow3 - so how do I make sure the shape that i add is called UpArrow3 - how to view its properties????

THanks in advance!!!!

Is it possible to assign a macro and a hyperlink to a shape I have placed on
a spreadsheet? The hyperlink seems to take precidence and the macro never

What I really want is for the macro to launch when the shape is clicked and
for the screen tips of the hyperlink to appear when the mouse hovers over the

Any suggestions.

hi everybody

Is it possible to make a dynamic tooltip in excel like when we hover on a particular cell. if yes pls tell me how to do it.
any help will be highly appreciated


With this code add comment in a cell.

Sub crearComment()
With ActiveCell
    .AddComment convertir_cadena(Range("TablaDocumentos"))
    With .Comment.Shape
        .Height = 354
        .Width = 116.25
    End With
    .Comment.Visible = True
End With

End Sub

The text have variable length. Is there a way to set: Comment.Shape.Height and Width, consistent with the length of the string? Is there a par Autosize method the Shape?

I would like to have a macro so that when a cursos passes over a shape, it runs a macro so that the colour of the shape changes.
Is this possible?

Is there a way to have a tooltip for each individual item in a listbox. I know how to have a tooltip for the overall list box object, but not for the individual line items.

I'm trying to show a simple tooltip over my images created from the Control
Toolbar when the users mouse over this.
I'm not having any luck finding code to modify to do this.
Any ideas?

Hi, everybody, Pls. suggest how to create a tooltip or texts to display when i select a picture inserted in excel.

Have an .xls file with multiple sheets and many objects (Shapes) on of those sheets.
The objects are all rectangles, some of which contain text frames, where I had entered manually some text.

Also, each shape has a name.

Now, I want my VBA Macro to take a specific shape (using its name), and find out if it has text frame in it.

i thought the way to do it is


Set oShp = ActiveSheet.Shapes(X)
If oShp.HasTextFrame Then  
   Msgbox oShp & "has text frame"
End If

,where X is the string that is the name of the shape I am testing.

Every example I could find had that type of code above.
But in my case I get "Object doesn't support this property or method" when I call this code.

Also, scanning through Shapes Collection properties, I don't actually see "HasTextFrame" mentioned. Only "HasDiagram".

Would anyone have any suggestions?