2

I have been looking all over the internet for a solution to this problem but for some reason I can never find anything directly related to using .onAction with selecting a specific cell.

I am using an answer to another question as a reference:

In the section where it is looping through shapes, the script assigns an .onAction event to each shape. Whenever this is run in Excel 2010 I get the error:

Cannot run the macro "SelectCell "Sheet 1","$C$10"". 
The macro may not be available in this workbook or all macros may be disabled.

I am new to VBA scripting for excel so I have no idea if it is the formatting, but I know it is related to this line.

.OnAction = "'SelectCell """ & ws.Name & """,""" & cll.Address & """'"

I created a sub-procedure for SelectCell to display the values being sent as a debug. Same error.

I tried having excel allow all macros and disable all macros but it had no effect on the error.

If anyone has any idea of where I am going wrong or any resources I can use to further educate myself, it would be greatly appreciated.

Community
  • 1
  • 1
Ambiguities
  • 415
  • 6
  • 18
  • 2
    Where is SelectCell defined - in a regular module, or in a sheet module? Does it run if you remove the parameters? – Tim Williams Oct 10 '14 at 18:14

2 Answers2

1

This (both subs in a regular module) works for me.

Sub SelectCell(sht As String, rng As String)
    ThisWorkbook.Sheets(sht).Range(rng).Select
End Sub

Sub Assign()
    ActiveSheet.Shapes(1).OnAction = "'SelectCell """ & _
            Selection.Parent.Name & """, """ & _
            Selection.Address() & """'"
End Sub

If SelectCell is in a sheet code module, then you need to include the sheet code name:

Sub Assign()
    ActiveSheet.Shapes(1).OnAction = "'Sheet1.SelectCell """ & _
            Selection.Parent.Name & """, """ & _
            Selection.Address() & """'"
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
-1

If I'm reading this right, "SelectCell" is a macro name, and you're passing in "Sheet 1","$C$10" as parameters (as strings). Whenever you make a call on the right side of an assignment operator (equals sign) the parameters need to be passed in with parenthases. for example, 

.OnAction = "SelectCell "Sheet 1","$C$10""

is wrong, and 

.OnAction = "SelectCell ("Sheet 1","$C$10")"

is right.

Try that, but there's not much I can go off without much code. You could also try to use the fully qualified macro name, in case the Macro is in another module.

John Smith
  • 7,243
  • 6
  • 49
  • 61
  • 1
    That does not work without the enclosing single-quotes, and it's not necessary to use parentheses when passing parameters to onAction. – Tim Williams Oct 10 '14 at 19:48
  • try to use the fully qualified macro name, see if that works. Ex- VBAproject.module1.selectCell – John Smith Oct 10 '14 at 20:10