Is there a way to disable multiple buttons at a time?

Question

I have a vba userForm that has 36 buttons on it. I would like to disable all buttons when the value on one of my newsletters reaches a certain number. Right now, with every click of the button, the number is incremented by one in the spreadsheet I am referring to. When the number reaches three, I would like to disable all buttons.

Answer 1

Dim oCtrl As Control Dim oButton As CommandButton For Each oCtrl In MyForm.Controls If oCtrl.Tag = "SomeValue" Then Set oButton = oCtrl oButton.Enabled = False End If Next Set oCtrl = Nothing Set oButton = Nothing 

If you have other buttons that you do not want to disable, the solution should use the Tag property. Set the Tag property on all buttons that you want to enable or disable along with the same value. Then you can check this value in view mode and enable / disable them. Another way is to call them the same prefix or suffix and check that in your code.

Adding

Btw, the Control object does not have the Enabled property. Therefore, you must throw it in the CommandButton to disable it . Apparently, the Control object has the Enabled property, but it does not appear in intellisense. However, you should still try to enable Control in the CommandButton to make sure you have one. Here is the extended version:

 Dim oCtrl As Control Dim oButton As CommandButton For Each oCtrl In MyForm.Controls If oCtrl.Tag = "SomeValue" Then On Error Resume Next Set oButton = oCtrl On Error GoTo 0 If Not oButton Is Nothing Then oButton.Enabled = False End If End If Next Set oCtrl = Nothing Set oButton = Nothing 

Answer 2

Put all the buttons in a Frame object, and then just turn off the whole frame. This will also disable everything inside the frame.

Alternatively, based on your last question, you can use this code:

 Dim counter As Integer Private Sub btn1_Click() CaptureImage (btn1.Name) End Sub Private Sub btn2_Click() CaptureImage (btn2.Name) End Sub Private Sub btn3_Click() CaptureImage (btn3.Name) End Sub Private Sub UserForm_Activate() counter = 1 End Sub Private Sub CaptureImage(ByVal btnName As String) Controls("capture" & counter).Picture = Controls(btnName).Picture counter = counter + 1 If counter > 3 Then DisableButtons End If End Sub Private Sub DisableButtons() Dim ctl As Control For Each ctl In UserForm1.Controls If Left(ctl.Name, 3) = "btn" Then ctl.Enabled = False End If Next End Sub 

Ideally, you want Control objects to be bound to buttons that Thomas offers.

Answer 3

@Mike: Try it -

 If Sheets("Sheet1").Range("A1").Value = 3 Then UserForm1.CommandButton1.Enabled = True Else UserForm1.CommandButton1.Enabled = False End If 

(Replace Sheet1 , A1 , UserForm1 and CommandButton1 with the correct ones for your book)