Onaction with paremeters

[bradyboyy88]

Why is this not working? Says object doesnt support this property or method. I am trying to pass a shape to the sub routine at on action but maybe my syntax is wrong with this onaction string??

Sub test()

With Sheets("Sheet1")
   .Shapes("Edit_Button").OnAction = "'UpdateButtonAction " & .Shapes("Edit_Button") & "'"
End With

End Sub

Sub UpdateButtonAction(UpdateButton As Shape)
    
    MsgBox UpdateButton.Name
    
End Sub

[code]

 

[bradyboyy88]

Also how would i do this if say i wanted to also even pass a second shape object as well to UpdateButtonAction.

 

[Krayons]

You are not using the .OnAction property correctly. As per the MSDN, .OnAction is used to return or set the name of a macro to be run when the button is clicked.

So with this line of code, you are trying to set the edit_button to run a macro with a malformed name.

.Shapes("Edit_Button").OnAction = "'UpdateButtonAction " & .Shapes("Edit_Button") & "'"

Proper code would be something like:

Sub test()

	With Sheets("Sheet1")
	
		.Shapes("Edit_Button").OnAction = "some_macro_name"
	
	End With

End Sub

Which would then set the "Edit_Button" to run a macro named "some_macro_name" when clicked.

I also do not see a reason to run that MSGBOX in its own subroutine. Just display the msgbox within the test() subroutine directly. Like so:

Sub test()

	With Sheets("Sheet1")
	
		.Shapes("Edit_Button").OnAction = "some_macro_name"
		MsgBox .Shapes("Edit_Button").OnAction
	
	End With

End Sub

 

[John_w]

You can't pass an object as an OnAction argument, only simple things like strings and numbers. Therefore you could pass the shape name like this:

Sub test()
    With Sheets("Sheet1").Shapes("Edit_Button")
        .OnAction = "'UpdateButtonAction " & Chr(34) & .Name & Chr(34) & "'"
    End With
End Sub

Sub UpdateButtonAction(shapeName As String)
    Dim shp As Shape
    Set shp = Sheets("Sheet1").Shapes(shapeName)
    MsgBox shp.Name
End Sub

 

[Krayons]

You can't pass an object as an OnAction argument, only simple things like strings and numbers. Therefore you could pass the shape name like this:

Sub test()
    With Sheets("Sheet1").Shapes("Edit_Button")
        .OnAction = "'UpdateButtonAction " & Chr(34) & .Name & Chr(34) & "'"
    End With
End Sub

Sub UpdateButtonAction(shapeName As String)
    Dim shp As Shape
    Set shp = Sheets("Sheet1").Shapes(shapeName)
    MsgBox shp.Name
End Sub

That will not work. Macro name is still malformed as macro names cannot contain single or double quotes.

Sub test()
    With Sheets("Sheet1").Shapes("Edit_Button")
        [COLOR="#FF0000"].OnAction = "UpdateButtonAction" & .Name[/COLOR]
    End With
End Sub

Sub UpdateButtonAction(shapeName As String)
    Dim shp As Shape
    Set shp = Sheets("Sheet1").Shapes(shapeName)
    MsgBox shp.Name
End Sub

Edit: @bradyboyy88 it would be best if you just tell us what it is you are trying to achieve.

 

[bradyboyy88]

That will not work. Macro name is still malformed as macro names cannot contain single or double quotes.

Sub test()
    With Sheets("Sheet1").Shapes("Edit_Button")
        [COLOR=#FF0000].OnAction = "UpdateButtonAction" & .Name[/COLOR]
    End With
End Sub

Sub UpdateButtonAction(shapeName As String)
    Dim shp As Shape
    Set shp = Sheets("Sheet1").Shapes(shapeName)
    MsgBox shp.Name
End Sub

Edit: @bradyboyy88 it would be best if you just tell us what it is you are trying to achieve.

The code i gave above is a simple form of something i wanted to achieve. Passing an object in a subroutine on action of pressing a shape button. The message box really is not what is going inside that subroutine but instead lots and lots of code that uses that shape object. I wonder why the onaction is limited to only numbers and strings as these are objects as well . I wonder why shapes are not included...


John is right in the sense that you can pass some variables but I would need to keep the subroutine contained in single quotes. Atleast that is what my research has shown me. I will just pass the names and reference them. I just really wanted to see why I couldnt pass the object but it looks like its not possible from what you have all stated.

 

[John_w]

That will not work. Macro name is still malformed as macro names cannot contain single or double quotes.

Sub test()
    With Sheets("Sheet1").Shapes("Edit_Button")
        [COLOR=#FF0000].OnAction = "UpdateButtonAction" & .Name[/COLOR]
    End With
End Sub

Sub UpdateButtonAction(shapeName As String)
    Dim shp As Shape
    Set shp = Sheets("Sheet1").Shapes(shapeName)
    MsgBox shp.Name
End Sub

I tested my code and it works perfectly. Your code above gives the error "Cannot run the macro 'UpdateButtonActionEdit_Button'. The macro may not be available....". Even adding the missing space between "UpdateButtonAction" and .Name gives the same (similar) error.

 

[bradyboyy88]

John,

What if I want to pass multiple string variables since now i need to name the shape and the sheet its located in?

 

[John_w]

I wonder why the onaction is limited to only numbers and strings as these are objects as well . I wonder why shapes are not included...

Numbers and strings are not objects; they are primitive (basic) data types. Shapes are objects.

 

[John_w]

John,

What if I want to pass multiple string variables since now i need to name the shape and the sheet its located in?

Each OnAction string argument must be inside quotes and numbers must be without quotes.

Try this:

Sub test()
    With Sheets("Sheet1").Shapes("Edit_Button")
        .OnAction = "'UpdateButtonAction " & Q(.Name) & "," & Q("Sheet1") & "'"
    End With
End Sub

Sub UpdateButtonAction(shapeName As String, sheetName As String)
    Dim shp As Shape
    Set shp = Sheets(sheetName).Shapes(shapeName)
    MsgBox shp.Name & " " & sheetName
End Sub

Private Function Q(text As String) As String
    Q = Chr(34) & text & Chr(34)
End Function