asked on

On an xl Cell, when a SHAPE with a screenTip is clicked by User, SHAPE .OnAction is disabled. Have Workaround but dont understand it.

'I .addPicture with OnAction like this:
With wsIF.Cells(rw, cl)
Set sh = wsIF.Shapes.AddPicture("D:\bla\myPic.jpg", msoCTrue, msoTrue, .Left, .Top + 1, -1, -1)
wsIF.Shapes(wsIF.Shapes.Count).Height = .Height
wsIF.Shapes(wsIF.Shapes.Count).OnAction = "Item_Files_Icons_onAction" 'drive a macro
End With
wsIF_dat.Cells(activeItemFile_rowNo_wsItemFile, 1) = sh.Name 'to data

'With help from Rgonzo1971, (ref EE : Q_28580565.html ) an icon to expresses a screenTip like this:
wsIF.Hyperlinks.Add Anchor:=wsIF.Shapes(wsIF.Shapes.Count), Address:="", ScreenTip:="blabla"

'But when I add both screenTip and OnAction to the same sh, onAction only works in the absence of a screenTip.

It turns out that this true: http://www.spreadsheet1.com/screentips--onaction.html
and although this url points to a solution, I dont have the expertise to understand what the solution is.

The use of Ribbon ctls is not an option, as the icons must be used in an xl sheet, where I need to build my dashboard.

Could someone guide me to a workaround please?

Thanks
Kelvin

ASKER CERTIFIED SOLUTION

Robberbaron (robr)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger

ASKER

Thank you - in haste..
I have this working just now, using Robberbaron's help. I need to break off for now and will report ASAP tonight.

**** I shall accept Robberbaron's solution as the sole solution.

Kelvin

Roger

ASKER

Thanks, Robberbaron,

Rated A for speed, and that you gave me access to the solution. Without some plain English, its doubtful that I would have attempted to elucidate the original description (clear though its meaning is after the event).
,
Kelvin

This is how it worked out.

The dashboardIcon "lighthouse" is on Sheet1.Cells(2,2); the dummy cell defined by the SubAddress is on Sheet2.Cells(2,2)
My dashboard will have a matrix of rows and columns of Icons ('lighthouse' is one) on Sheet1, each mirrored for position in Sheet2, with its subAddress.

''''ThisWorkbook events
''''''for safety, ensure that Sheet2.Celles(2,2) is NOT activated (? not essential given the repeat line in emulateOnAction(), but why not?)
Private Sub Workbook_Open()
subAddress_wsDummy_Event_on = False 'essential to control when Private Sub Workbook_SheetSelectionChange fires
Set ws = ThisWorkbook.Sheets("Sheet1")
Set wsDummy = ThisWorkbook.Sheets("Sheet2")
wsDummy.Range(wsDummy.Cells(1, 1), wsDummy.Cells(1, 1)) = ""
subAddress_wsDummy_Event_on = True
End Sub

'''''Detect change in Sheet2 and fire emulateOnAction
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If subAddress_wsDummy_Event_on = True And Sh.Name = "Sheet2" Then
MsgBox "sh.name = " & Sh.Name & "; selection_change event; row = " & Target.Row & "; column = " & Target.Column
emulateOnAction
End If
End Sub

'''''create dashboard shape:
Sub subAddressMethod()
Dim Sh As Shape
subAddress_wsDummy_Event_on = False
With ws.Cells(2, 2)
Set Sh = ws.Shapes.AddPicture("D:\path\lighthous.jpg", msoCTrue, msoTrue, .Left, .Top, 20, 16)
ws.Hyperlinks.Add anchor:=ws.Shapes(ws.Shapes.Count), Address:="", SubAddress:="Sheet2!B2", ScreenTip:="sub blabla"
End With
subAddress_wsDummy_Event_on = True
End Sub

''''''called from Private Sub Workbook_SheetSelectionChange
Sub emulateOnAction()
subAddress_wsDummy_Event_on = False
wsDummy.Range(wsDummy.Cells(1, 1), wsDummy.Cells(1, 1)).Activate 'Move activated cell away from Sheet2.Cells(2,2) to fire repeatedly!
subAddress_wsDummy_Event_on = True
MsgBox "Now do your code for clicking that icon"
End Sub

Roger

ASKER

one last comment, above I wrote: "My dashboard will have a matrix of rows and columns of Icons ('lighthouse' is one) on Sheet1, each mirrored for position in Sheet2, with its subAddress."

This is not necessary. The position of the cell in sheet2 is irrelevant. So all shapes can use the same subAddress.

Roger

ASKER

THE FINAL COMMENT: in last comment, the text: "This is not necessary. The position of the cell in sheet2 is irrelevant. So all shapes can use the same subAddress." is nonsense!

The only way to know which shape has been clicked is to detect the subAddress that is activated as a consequence.

In case anyone reads this, apols!