Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Checking for existence of command button

    I know how to programmatically create a command button... size, text and all that... but how do I check for its existence in order to create it? I want to check to see if someone has mistakenly deleted the button so that I can create it. TIA

    DennisK

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi

    If it was a Form button, you could use something like this..
    Code:
    Sub checkShape()
    
    On Error Resume Next
    zShapeID = ActiveSheet.Shapes("myButton").ID
    If IsEmpty(zShapeID) Then
    'add..
    ActiveSheet.Buttons.Add(380.25, 47.25, 111, 28.5).Select
    Selection.OnAction = "myRoutine"
    Selection.Name = "myButton"
    Selection.Text = "click here for xxxx"
    Range("A1").Select
    End If
    On Error GoTo 0
    
    End Sub
    does that help?

    zeddy

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Dennis,

    Well Zeddy beat me to the punch again! Well anyway here's another method and it does allow for more than one button to be checked.
    Code:
    Option Explicit
    
    Sub CkCmdBtns()
    
       Dim zMYButton(2, 2) As String
       Dim Btn             As Button
       Dim iCntr           As Integer
       Dim zBadBtns        As String
       
    '*** Initialize array with button CAPTIONS ***
       zMYButton(0, 0) = "Copy Button"
       zMYButton(1, 0) = "Paste Button"
       
    '*** Initialize array with button status as BAD ***
       For iCntr = 0 To UBound(zMYButton) - 1
          zMYButton(iCntr, 1) = "BAD"
       Next iCntr
       
       For Each Btn In Worksheets("Sheet1").Buttons
          For iCntr = 0 To UBound(zMYButton) - 1
            If Btn.Caption <> zMYButton(iCntr, 0) And _
               zMYButton(iCntr, 1) = "BAD" Then
              zMYButton(iCntr, 1) = "OK"
              zBadBtns = zBadBtns & zMYButton(iCntr, 0) & vbCrLf
              Exit For
            End If
          Next iCntr
       Next Btn
       
       MsgBox "The following buttons:" & vbCrLf & vbCrLf & _
              zBadBtns & vbCrLf & "Have been Deleted", _
              vbOKOnly + vbCritical, _
              "Error: User Action Alert!"
    
    End Sub   'VCkCmdBtns()
    FIndButtons.JPG

    Test File: VBA - Excel - Test for Existance of Buttons on a Sheet.xlsm

    UPDATE: Almost forgot that this also works on FORM controls NOT ActiveX!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanx so much to both of you... I used Zeddy's approach because I really didn't need to display a message about it being deleted. Just that it was gone and as such... recreated the button... FYI..
    Code:
    Private Sub chkShape7()
        On Error Resume Next
        zShapeID = ActiveSheet.Shapes("Button 7").ID
        If IsEmpty(zShapeID) Then CreateButton7
        On Error GoTo 0
    End Sub
    Code:
    Sub CreateButton7()
        ActiveSheet.Buttons.Add(143, 10, 143, 40).Select
        Selection.Name = "Button 7"
        Selection.OnAction = "PrintMenu"
        ActiveSheet.Shapes("Button 7").Select
        Selection.Characters.Text = "Print Menu"
        ActiveSheet.Shapes.Range(Array("Button 7")).Select
        With Selection.Font
            .Name = "Times New Roman"
            .Size = 18
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        End With
        Range("B7").Select
    End Sub
    DennisK
    Last edited by RetiredGeek; 2015-04-18 at 16:14. Reason: Added Code Tags

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, no need to select anything there:
    Code:
    Sub CreateButton7()
        With ActiveSheet.Buttons.Add(143, 10, 143, 40)
            .Name = "Button 7"
            .OnAction = "PrintMenu"
            .Caption = "Print Menu"
            With .Font
                .Name = "Times New Roman"
                .Size = 18
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
            End With
        End With
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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