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

[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

 

[Tom Ogilvy]

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.

 

[brettmanning24]

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 -

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

 

[Tom Ogilvy]

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

 

[brettmanning24]

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 -

Thank you, this is perfect.