Enable / Disable Command Button depending on a cell's value

  • Thread starter Thread starter brettmanning24
  • Start date Start date
B

brettmanning24

Hello,

I would like to disable / enable depending on a Cells value. For
example if cell A1 = "OK" enable the command button, if the Cell
equals anything else I would like it disabled.

Public Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Address = "Home!$C$13" Then
If .Value = "OK" Then
Me.OLEObject("cmdFX").Enabled = True
Else
Me.OLEObject("cmdFX").Enabled = False
End If
End If
End With
End Sub

My sheet name is called "HOME", I have tried the VB code above but it
does not work. Can anyone help?

Thanks in advance.

Brett
 
Assuming this is in the sheet module for the sheet Home:

Public Sub Worksheet_Change(ByVal Target As Range)
If Target.count > 1 then exit sub
With Target
If .Address = "C$13" Then
If .Value = "OK" Then
Me.OLEObject("cmdFX").Enabled = True
Else
Me.OLEObject("cmdFX").Enabled = False
End If
End If
End With
End Sub

You said A1, but your code is keying on the value in C13.
 
Assuming this is in the sheet module for the sheet Home:

Public Sub Worksheet_Change(ByVal Target As Range)
If Target.count > 1 then exit sub
With Target
If .Address = "C$13" Then
If .Value = "OK" Then
Me.OLEObject("cmdFX").Enabled = True
Else
Me.OLEObject("cmdFX").Enabled = False
End If
End If
End With
End Sub

You said A1, but your code is keying on the value in C13.

--
Regards,
Tom Ogilvy











- Show quoted text -
Click to expand...

Hello,

Thank you for your reply.

I am using an IF statement in Cell C13, the IF statement is below.

=IF(ISERROR(SUM(FX!B4:G36)),"ERRORS FOUND","OK")

The button enables if I manually type in "OK" and disables if I enter
something other then "OK". The enable/disable does not work using the
IF statement though. Can you offer any advice?

Thanks again.

Brett
 
Use the calculate event.
Private Sub Worksheet_Calculate()
If me.Range("C13").Value = "OK" Then
Me.OLEObject("cmdFX").Enabled = True
Else
Me.OLEObject("cmdFX").Enabled = False
End If
End Sub
 
Use the calculate event.
Private Sub Worksheet_Calculate()
If me.Range("C13").Value = "OK" Then
Me.OLEObject("cmdFX").Enabled = True
Else
Me.OLEObject("cmdFX").Enabled = False
End If
End Sub

--
Regards,
Tom Ogilvy










- Show quoted text -
Click to expand...

Thank you, this is perfect.
 
Back
Top