Error 91 when counting and adding commandButton

Hi selector,

Try the following code tested using Excel 2003. However, to actually use the Command Button is a lot more complicated. To do that you probably need a 'Class Module'. See the following (excellent) example, which will probably make your head spin several times.
http://stackoverflow.com/questions/1...g-vba-on-excel

Lewis

Sub CreateDynamicCommandButton()

  Dim Button As CommandButton

  'Display the Userform - not necessary if UserForm is already activated
  UserForm1.Show False


  Set Button = UserForm1.Controls.Add("Forms.CommandButton.1")

  Debug.Print UserForm1.Controls.Count
  With Button
      .Caption = "New button"
      .Width = 72
      .Height = 24
      .Left = 12
      .Top = 58
  End With
End Sub

I apologize for misreading your original question. I thought you wanted to add a Control dynamically, not change it's properties.

The following may help you in the future.

Lewis

You can change CommandButton1 properties as follows:

Sub ModifyCommandButton1A()
  
  UserForm1.Show False

  With UserForm1.CommandButton1
    .Caption = Format(Now(), "hh:mm:ss")
    .Width = 72
    .Height = 24
    .Left = 12
    .Top = 58
  End With
  
End Sub

Another way similar to yours is:

Sub ModifyCommandButton1B()
  
  Dim myButton As Control
  Dim cCntrl As Control
  Dim i As Integer
  
  UserForm1.Show False
  
  i = 0
  For Each cCntrl In UserForm1.Controls
    If cCntrl.Name = "CommandButton1" Then
      Set myButton = cCntrl
      Exit For
    End If
  Next cCntrl
  

  Debug.Print myButton.Name
 
  With myButton
    .Caption = Format(Now(), "hh:mm:ss")
    .Width = 72
    .Height = 48
    .Left = 12
    .Top = 58
  End With
  
End Sub

You may find the following routine useful:

Sub LoopThruUserForm1Controls()

  Dim cCntrl As Control
  Dim i As Integer
  Dim sData As String
  
   UserForm1.Show False
  
    i = 0
    For Each cCntrl In UserForm1.Controls
      i = i + 1
      sData = Format(Format(i, "####"), "@@@@  ") & _
              Format(TypeName(cCntrl), "!@@@@@@@@@@@@@@") & _
              Format(cCntrl.Name, "!@@@@@@@@@@@@@@@@") & _
       "H=" & Format(Format(cCntrl.Height, "0.00   "), "@@@@@@@@@") & _
       "W=" & Format(Format(cCntrl.Width, "0.00   "), "@@@@@@@@@") & _
       "T=" & Format(Format(cCntrl.Top, "0.00   "), "@@@@@@@@@") & _
       "L=" & Format(Format(cCntrl.Left, "0.00   "), "@@@@@@@@@")
      Debug.Print sData
      
   Next cCntrl
End Sub

And finally, if you want to see if a control exists:

Sub TestUserFormControlExists()
  
  Dim i As Integer
  Dim bExists As Boolean
  Dim sControlName As String

  UserForm1.Show False
  
  For i = 1 To 3
    sControlName = "CommandButton" & i
    bExists = DoesUserForm1ControlExist(sControlName)
    MsgBox "UserForm Control '" & sControlName & "' EXISTS = " & bExists
  Next i
  
  UserForm1.CommandButton2.Visible = False
  sControlName = "CommandButton2"
  bExists = DoesUserForm1ControlExist(sControlName)
  MsgBox "Invisible UserForm Control '" & sControlName & "' EXISTS = " & bExists
  
End Sub

Public Function DoesUserForm1ControlExist(sControlName As String) As Boolean
  'This returns TRUE if control exists on the userform
    
  Dim cCntrl As Control
    
  Dim iError As Long
  
  On Error Resume Next
  Set cCntrl = UserForm1.Controls(sControlName)
  iError = Err.Number
  On Error GoTo 0
    
  If iError = 0 Then
    DoesUserForm1ControlExist = True
  End If
    
End Function