How do you programmatically hide/unhide a command button in Excel?

[MacNut2004]

Hello,

I have a command button in excel that i want to be hidden initially, until
another cell in the worksheet is a 3. How do I go about doing this in VBA?

Thank you!
MN

 

[Dave Peterson]

If the value in the cell changes to 3 based on the user typing, I'd use a
Worksheet_Change event.

If you want to try, rightclick on the sheet tab that should have this behavior
and select View Code. Paste this into the code window that opened (usually on
the right hand side of the screen):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("A1")
If Intersect(myCell, Target) Is Nothing Then
Exit Sub
End If

If myCell.Value = 3 Then
Me.CommandButton1.Visible = True
Else
Me.CommandButton1.Visible = False 'rehide it if the value changes???
End If
End Sub

On the other hand, if the value changes as a result of a formula, you could use
a different worksheet event:

Option Explicit
Private Sub Worksheet_Calculate()

Dim myCell As Range
Set myCell = Me.Range("A1")

If myCell.Value = 3 Then
Me.CommandButton1.Visible = True
Else
Me.CommandButton1.Visible = False 'rehide it if the value changes???
End If
End Sub