Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to get the button object with its caption ?

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,495
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Ramana,

    Welcome to the lounge as a new poster.

    This code should do what you want.
    Code:
    Option Explicit
    
    Sub DeleteButton()
    
       Dim shp As Shape
       
       For Each shp In ActiveSheet.Shapes
          If shp.AutoShapeType = -2 Then
            If shp.AlternativeText = "Go To Master" Then
              shp.Delete
            End If
          End If
       Next shp
    End Sub
    I've attached a test workbook that has 3 buttons, one named as indicated, and another shape with the same caption to make sure it only deletes buttons. The code is designed to run on the active worksheet so just press Alt+F8 and select DeleteButton to test. You can copy the Master sheet to retrun the test.

    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2013-02-10)

  4. #3
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,352
    Thanks
    49
    Thanked 275 Times in 253 Posts

    Good for Form Buttons

    Since a form button's text (text on the button) and alternative text (text seen if button not able to be drawn) may be 2 different values, using the Text may be the safer approach. Here is a slight modification of RG's code to do so. Note: that both RG's and this code will work for Form controls only, not active X controls. If there is at least 1 active X control on the worksheet, the code will fail. Since the word "Caption" was mentioned, sounds like there may be an active X control and you will need the second code. Note: it does not matter with the second code if there are both form and active X controls present.
    HTH,
    Maud
    Code:
    Public Sub DeleteFormButton()
       Dim shp As Shape
       
       For Each shp In ActiveSheet.Shapes
            shp.Select
            If Selection.Characters.Text = "Go to Master" Then
                shp.Delete
            End If
       Next shp
    End Sub
    Code:
    Public Sub DeleteCMDButton()
    Dim cmd As Object
     
    With ActiveSheet
        For Each cmd In .OLEObjects
            If TypeName(cmd.Object) = "CommandButton" Then
                If cmd.Object.Caption = "Go to Master" Then
                    cmd.Delete
                End If
            End If
        Next
    End With
    End Sub
    Last edited by Maudibe; 2013-02-10 at 09:27.

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2013-02-10)

  6. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,939
    Thanks
    0
    Thanked 94 Times in 90 Posts
    You can also just loop through the buttons specifically
    Code:
    for each btn in Activesheet.Buttons
       if btn.Caption = "Go To Master" Then btn.Delete
    next btn
    for Forms buttons (since I know from your post elsewhere that they are not ActiveX buttons). Note there is no need to select either type of button to manipulate it
    Regards,
    Rory
    Microsoft MVP - Excel.

  7. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2013-02-10)

  8. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,352
    Thanks
    49
    Thanked 275 Times in 253 Posts
    Sorry Guys,
    Wasn't sure what type of button was being used (kv's first post). rory, you sure summed it up...very nice but do form buttons have a caption property?
    Last edited by Maudibe; 2013-02-11 at 06:35.

  9. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    Hi

    Another way might be to not copy the button in the first place when copying the columns from sheet1 to different sheets.
    If you don't want to copy the button, use code like this..

    Application.CopyObjectsWithCells = False

    ..you can turn copy object mode back on with..

    Application.CopyObjectsWithCells = True

    You can also save current setting with..
    zMode = Application.CopyObjectsWithCells

    ..then turn it on or off as required,

    ..and then reset it back with
    Application.CopyObjectsWithCells =zMode

    zeddy

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •