| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable / Disable Command Button depending on a cell's value
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enable / Disable Command Button depending on a cell's value
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 " wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enable / Disable Command Button depending on a cell's value
On 24 Aug, 15:00, Tom Ogilvy
wrote: 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 " wrote: 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- Hide quoted text - - 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enable / Disable Command Button depending on a cell's value
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 " wrote: On 24 Aug, 15:00, Tom Ogilvy wrote: 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 " wrote: 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- Hide quoted text - - 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Enable / Disable Command Button depending on a cell's value
On 24 Aug, 16:32, Tom Ogilvy
wrote: 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 " wrote: On 24 Aug, 15:00, Tom Ogilvy wrote: 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 " wrote: 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- Hide quoted text - - 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- Hide quoted text - - Show quoted text - Thank you, this is perfect. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to disable/enable command button(ActiveX) using macro | Excel Discussion (Misc queries) | |||
| Enable/Disable Command Button | Excel Programming | |||
| Enable / Disable a button using a macro | Excel Programming | |||
| How to enable/disable a button. | Excel Programming | |||
| Enable/Disable Button | Excel Programming | |||
Hybrid Mode
