How to pass variable through a buttons .OnAction property

[ronnyfo]

Hi all
I've been struggling with this for while now and i hope you can help..
I am trying to pass 3 parameters thru a button's .onaction property to a macro.

(Can't belive Microsoft made it this difficult!!)


My macro is like this:
----------------------------------------------------------
Sub test()
'Creates a button at cell A1

Cells(1, 1).Select
Row = ActiveCell.Row
Column = ActiveCell.Column
ID = "123-AA-123"

Dim btn1 As Object
Set btn1 = ActiveSheet.Buttons.Add(ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height)
With btn1
.Caption = "Check"
.OnAction = "'check ""row"", ""column"",""ID"" '" 'this is where the problem is..
End With
End Sub

Sub check(r, c, i)
'Recieves values from the button's .OnAction property
MsgBox "Button " & i & " is at row=" & r & ", column=" & c
End Sub

-----------------------------------------------------------
What happens is of course that no values are passed over - Only the name of the variables..

Anyone got any ideas on how i can pass the values over?

thanks in advance

 

[xld]

Sub test()
'Creates a button at cell A1
    
    Cells(1, 1).Select
    Row = ActiveCell.Row
    Column = ActiveCell.Column
    ID = "123-AA-123"
    
    Dim btn1 As Object
    Set btn1 = ActiveSheet.Buttons.Add(ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height)
    With btn1
    .Caption = "Check"
    .OnAction = "'check " & Row & ", " & Column & ",""" & ID & """'"
    End With
End Sub

Sub check(r, c, i)
'Recieves values from the button's .OnAction property
MsgBox "Button " & i & " is at row=" & r & ", column=" & c
End Sub

 

[ronnyfo]

Thanks!!!

I have however also come up with a work-around, where i get the cell position/location of the button in the recieving macro.

I am posting it here in case it can help others


the macro is then:
-----------------------
Sub test()
'Creates a button at cell A1

Cells(1, 1).Select
Row = ActiveCell.Row
Column = ActiveCell.Column
ID = "123-AA-123" 'OK, i am cheating here,
Cells(1, 2) = "123-AA-123" 'but the button is always placed on the left of the ID-cell

Dim btn1 As Object
Set btn1 = ActiveSheet.Buttons.Add(ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height)
With btn1
.Caption = "Check"
.OnAction = "'Check'"
End With
End Sub

Sub check()
'Recieves values from the button's .OnAction property
Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell 'get the cell position of the button
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Select
r = ActiveCell.Row
c = ActiveCell.Column
i = Cells(r, c + 1).Text
'
MsgBox "Button " & i & " is at row=" & r & ", column=" & c
End Sub

 

[xld]

I thought of that, but you can't get the ID that way.