hi,
i have a few command buttons created directly on the spreadsheet
(not through userforms). i need to have the buttons appears and
disappear depending on a cell's value. ex. if the cell value = 1, have
the buttons become visible and invisible if any other value. visible
or enabled..either is fine.
the code i am using is this but i can't seem to get it to work. i am
using excel 2002. appreciate any help. thanks.

Private Sub Worksheet_Activate()
If Target.Address = "n40" and Target.Value = 1 Then
Worksheets("Sheet1").CommandButton("button1").Enab led = True
Else
Worksheets("Sheet1").CommandButton("button1").Enab led = False
End If
End Sub

Try

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Address = "$N$40" And .Value = 1 Then
Me.Buttons("Button 1").Enabled = True
Else
Me.Buttons("Button 1").Enabled = False
End If
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"yoram" wrote in message
oups.com...
hi,
i have a few command buttons created directly on the spreadsheet
(not through userforms). i need to have the buttons appears and
disappear depending on a cell's value. ex. if the cell value = 1, have
the buttons become visible and invisible if any other value. visible
or enabled..either is fine.
the code i am using is this but i can't seem to get it to work. i am
using excel 2002. appreciate any help. thanks.

Private Sub Worksheet_Activate()
If Target.Address = "n40" and Target.Value = 1 Then
Worksheets("Sheet1").CommandButton("button1").Enab led = True
Else
Worksheets("Sheet1").CommandButton("button1").Enab led = False
End If
End Sub

i tried this and keep getting a "Method 'Buttons' of object
'_Worksheet' failed" error...any ideas?

Perhaps it is buttons from the control toolbox

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Address = "$N$40" And .Value = 1 Then
Me.OLEObjects("CommandButton1").Enabled = True
Else
Me.OLEObjects("CommandButton1").Enabled = False
End If
End With

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"yoram" wrote in message
ups.com...
i tried this and keep getting a "Method 'Buttons' of object
'_Worksheet' failed" error...any ideas?

that works in changing the button to inactive but unfotunately it
changes to inactive as soon as any cell value is changed. it does not
change back to active regardless of the linked cell's value.

Unless the linked cell is N40 I don't see the connection. That code will
only toggle the button state depending on the change of the value in N40 to
1 or not 1.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"yoram" wrote in message
oups.com...
that works in changing the button to inactive but unfotunately it
changes to inactive as soon as any cell value is changed. it does not
change back to active regardless of the linked cell's value.