Command Button Position

[foxtrot23]

I'm trying to create a command button that when clicked, passes it's location through to the on click procedure (I eventually want to use it to delete the row that it is in). I supposedly can make use of ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row to do this but I am getting an error. My procedure at the moment is as follows:

Private Sub CommandButton1_Click()

    MsgBox ("Row of pressed button: " & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row)

End Sub

When I hit the button and the procedure is run, I get the error Run-time error '13' Type Mismatch.

I'm obviously doing something wrong here. Any assistance would be greatly appreciated.

 

[Gary McMaster]

I suspect you are using an "ActiveX" button instead of a "Forms" button. The code you have shown is for a "forms" button.

Gary

 

[Andrew Poulsom]

Welcome to MrExcel.

You can't use Application.Caller with a CommandButton from the Control Toolbox. Try instead:

Private Sub CommandButton1_Click()
    MsgBox "Row of pressed button: " & CommandButton1.TopLeftCell.Row
End Sub

 

[foxtrot23]

Thanks so much for your responses - they're much appreciated. After reading them I figured that an ActiveX button probably suits my purposes better anyway. Being a newbie to Excel I'm still feeling my way around!

 

[jim may]

foxtrot23:

You say "Being a newbie to Excel I'm still feeling my way around!"

Well..

Me being an oldie to Excel I'm still feeling my way around!

Jim

 

[mikerickson]

The type mismatch is that you are trying to cocatenate a string and a range object. Try adding .Address

MsgBox "Row of pressed button: " & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row[COLOR="Red"].Address[/COLOR]

 

[Andrew Poulsom]

The type mismatch is that you are trying to cocatenate a string and a range object. Try adding .Address

MsgBox "Row of pressed button: " & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row[COLOR=red].Address[/COLOR]

Did you try that Mike? Application.Caller won't work with ActiveX controls.