How to get the button object using its caption ?

**kvramana82** · 02-08-2013, 08:02 AM

Hi All,

I have a button in Sheet1 with caption "Go To Master". Now i want to find this button with its caption (Not with Button Name).

As per my requirement i am copying some columns from sheet1 to different sheets and these columns include this button.

In some cases i don't want to have this button. So wherever it is applicable i want to find this button with its caption and delete it.

Thanks & Regards,

Ramana

**OllieB** · 02-08-2013, 08:12 AM

Ramana, you will have to loop through the Shapes or OLEObjects collection for the worksheet object (depending on whether you have a Forms or ActiveX type of button), and identify the correct button by looking at the caption displayed

**kvramana82** · 02-08-2013, 08:23 AM

Hi OllieB,

Thanks for the quick response..
Can you please let me know the exact code to loop through shapes. its a form control in a sheet not ActiveX control.

Activesheet.shapes(i).name = "?"

As far as i know its a button name, I cannot give caption name here. Its impposible for me to find the object name as i keep pasting the columns.
I only way i can see is using caption name i have to find the button name.

I am just getting an idea if i try using ActiveX control then is there a way to find the object using caption.

Can you please suggest a way?

Thanks & Regards,

Ramana

**jraj1106** · 02-08-2013, 08:19 AM

Hi Ramana.

I m not sure if this is what you want but just check:

Sub COPYBUTTON()
    If Sheet1.CommandButton1.Caption = "Go To Master" Then
        ActiveSheet.Shapes("CommandButton1").Select
        Selection.Delete
    End If
End Sub

**kvramana82** · 02-08-2013, 09:09 AM

hi jraj1106,

Thanks for the reply. This is not exactly i am looking. I think your code is for ActiveX control.
To solve my issue I believe that i should replace form control button with ActiveX control button.

Thanks,
Ramana

**JosephP** · 02-08-2013, 09:12 AM

for instance

dim btn as button
for each btn in activesheet.buttons
if btn.caption = "Go To Master" Then btn.delete
next btn

**OllieB** · 02-08-2013, 09:29 AM

Something like

     Dim objShape As Shape
     For Each objShape In ActiveSheet.Shapes
          If objShape.TextFrame.Characters.Text = "Go To Master" Then
               objShape.Delete
          End If
     Next objShape