Help with Tooltip on clipart

[dgold82]

I have a picture of a printer on my worksheet that activates the following
macro. Can someone help me add code to make a tooltip that would display the
words "click to print" when a user hovers the mouse over the image:

Sub PrintSheet()
Application.Dialogs(xlDialogPrint).Show
' Range("AK6").Select
End Sub

Thanks.

 

[Jacob Skaria]

Try the below.

1. Select a cell in the same sheet which you are sure is not going to be
used..From menu Insert>Name>Define. Assign a name say 'nmtooltip'

2. Right click on the image>Hyperlink. Click Screen Tip button to add your
tip. Select the 'place in document' as the the defined name 'nmtooltip'

3. Right click the sheet tab. View code and paste the below code and save.
You dont need to assign macro to the picutre. Instead the PrintSheet will be
called from the selection change event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("nmToolTip")) Is Nothing Then
Call PrintSheet
Application.Goto Range("A1")
End If
End Sub


If this post helps click Yes

 

[r]

Sub test()
Dim s As Shape

Set s = ActiveSheet.Shapes("Picture 1")
ActiveSheet.Hyperlinks.Add Anchor:=s, Address:= _
"", SubAddress:="AK6", ScreenTip:="click to print"
s.OnAction = "PrintSheet"
End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Peter T]

maybe replace
SubAddress:="AK6",
with
SubAddress:=s.TopLeftCell.Address

Regards,
Peter T

 

[dgold82]

Thanks Jacob, but I got a compile error...Any ideas?

 

[Jim Cone]

Hi Peter,
A sub-address is not really required as "" will work.
However, the problem I've never been able to solve is that the assigned
macro is ignored when a hyperlink/screen tip is attached to the shape.
--
Jim Cone
Portland, Oregon USA


"Peter T" <peter_t@discussions>
wrote in message
maybe replace
SubAddress:="AK6",
with
SubAddress:=s.TopLeftCell.Address
Regards,
Peter T

 

[Peter T]

Hi Jim,

I really thought I had tested before posting but you are right!

Jocob's Application.Goto Range("A1") could be to a cell under the shape.

Regards,
Peter T

 

[dgold82]

This might be a stupid question, but how do I find my clipart/picture name.
You have "Picture 1" below--not sure what to put for my code. Thanks.

 

[dgold82]

Figuired it out using Jacob's method! Just tweaked the code a bit:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("nmToolTip")) Is Nothing Then
Application.Dialogs(xlDialogPrint).Show
Application.Goto Range("A1")
End If
End Sub

 

[r]

Sub Picture_Name()
Dim v

For Each v In ActiveSheet.Shapes
Debug.Print v.Name
Next

End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[r]

well! I learned something
regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[r]

however, my solution does not work
Watch the intervention of Jim Cone
regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Jacob Skaria]

Sub Macro()
Dim obj As Object
For Each obj In ActiveSheet.DrawingObjects
Debug.Print obj.Name
Next
End Sub


If this post helps click Yes