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

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
 
T

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.
 
B

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
 
T

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
 
B

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top