Activating/De-activating buttons

[Guest]

Is there a way to activate a macro button based on a value in a cell and then
de-activating the button if the value does not match?

Thanks,

 

[abcd]

if the button objet vba's name is commandbutton1 on the Sheet1 object then:

Sheet1.CommandButton1.Enabled = false ' or true

the change event of this sheet may be used to detect a change on that sheet

example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("A1")) Is Nothing) Then
CommandButton1.Enabled = Not CommandButton1.Enabled
End If
End Sub

 

[Guest]

How do I find the name of the button? Thanks,

 

[abcd]

Usually, the name is written inside the button when created. But you
also can find it in the VBA editor (the dropdown list: as you have
"General" or "worksheet" you now have the button object in the list. )

 

[William Benson]

If you mean a macro button on a command bar, I do not know how to do that
because I think they are either visible (added to the command bar) or not. I
don't think they can be disabled.


Assuming the "macro button" is a command button on a worksheet, yes.

Private Sub Worksheet_Change(ByVal Target As Range)

'test this by changing the value in cell A1
Const strTestValue = 5 'For example only

If ActiveSheet.Range("A1") = AcceptValue Then
CommandButton1.Enabled = True
Else
CommandButton1.Enabled = False
End If

End Sub