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. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,235
    Thanks
    202
    Thanked 796 Times in 729 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


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

    Maudibe (2013-02-10)

  5. #3
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,213
    Thanks
    46
    Thanked 231 Times in 212 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 08:27.

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

    RetiredGeek (2013-02-10)

  7. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 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.

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

    RetiredGeek (2013-02-10)

  9. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,213
    Thanks
    46
    Thanked 231 Times in 212 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 05:35.

  10. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,494
    Thanks
    28
    Thanked 171 Times in 167 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
  •