get caption of the button you click

[pankajy18]

how to get the caption of the button that is clicked say in a cell

Also how to extract the number from it

Please Suggest something

[AndyLitch]

Private Sub CommandButton2_Click()
    Sheet2.Range("A1").Value = CommandButton2.Caption
End Sub

[pankajy18]

Thanks AndyLitch ........sorry for not explaining in detail but the problem is a bit different......

I want to use a common macro for a number of buttons and hence i don't know the button name i.e. i can't use CommandButton2.Caption.

Any suggestions

[Norie]

If the buttons are Form buttons assign this macro to each of them.

Sub GetCaption()
Dim btn As Button

        Set btn = ActiveSheet.Buttons(Application.Caller)

        MsgBox "The caption of the button you clicked is .... " & btn.Caption

End Sub

[pankajy18]

Thanks Norie.....exactly what i needed

Is there a way such that i can take the number in Caption string....say UPLOAD19 is caption how can i get number 19 from it.......

[Norie]

Does the caption always begin with UPLOAD?

If it does then you can use Mid.

intCapNo = Mid(ActiveSheet.Buttons(Application.Caller), 7)

[pankajy18]

PLEASE EXPLAIN....ITS giving Error 438
object doesn't support this property or method

[pankajy18]

Thanks a lot norie

[Norie]

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[pankajy18]

PFA the excel file....attach document_10th.xls

i want to use this number for my upload and delete button code.....where i can delete the inserted oleobject by taking reference from the number.

[Norie]

Sorry, I'm not following.

Where/when are you getting the error you describe in the previous post?

Is it actually in the code I posted?

[pankajy18]

click UPLOAD1 and u will get it....

[Norie]

It's a typo, it should be this.

intCapNo = Mid(ActiveSheet.Buttons(Application.Caller).Caption, 7)