Checking for existence of command button

Topic

New Reply

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. :confused: TIA

DennisK

Reply | Quote

Replies

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

Reply | Quote

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()

40249-FIndButtons

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

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

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote