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.

thanks for your help. your code works in a new sheet but doesn't in my
existing form. i don't know if it has anything to do with the fact
that N40 evaluates as a result of an if/then formula checking the value
of a drop down list box.

Excel 97?

--

HTH

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


"yoram" wrote in message
oups.com...
thanks for your help. your code works in a new sheet but doesn't in my
existing form. i don't know if it has anything to do with the fact
that N40 evaluates as a result of an if/then formula checking the value
of a drop down list box.

no, excel 2002. even in a new instance of excel...the code only works
if I manually change the value of N40. in my scenario, i have a drop
down list created using data validation. it has three choices and N40
is coded to evaluate to "1" if choice 2 is selected; "0" otherwise. in
this case the code for disabling the commandbutton does not work
correctly. hopefully someone can help figure this out as i've been
scratching my head for a while now.

So is N40 a formula pointing at another cell that has the CV in? If so, what
is that formula, and what are the DV conditions?

--

HTH

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


"yoram" wrote in message
oups.com...
no, excel 2002. even in a new instance of excel...the code only works
if I manually change the value of N40. in my scenario, i have a drop
down list created using data validation. it has three choices and N40
is coded to evaluate to "1" if choice 2 is selected; "0" otherwise. in
this case the code for disabling the commandbutton does not work
correctly. hopefully someone can help figure this out as i've been
scratching my head for a while now.

no this is 2002. even in the new sheet the code works fine if n40 is
manually changed. but if the change is result of a an if/then
calculation, it does not work correctly. i have a data validation drop
down box with four choices. N40 is set to change to "1" if choice 2 is
selected. in this scenario the disable commandbutton code does not
work. hopefully there will be some other suggestions from people..as
i've been scratching my head about this for a while.

ok, cell N37 is data validated with the 'list' condition. the list has
three three text options,"weekly","monthly","yearly". N40 has this
formula: if(N37="monthly",1,"0"). so if "monthly" is the option the
user chooses, i want the command button to be enabled and disabled at
all other times. btw N37 is a merge of three cells for formatting
purposes...i doubt that would make any difference though.

--

HTH

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


"yoram" wrote in message
oups.com...
ok, cell N37 is data validated with the 'list' condition. the list has
three three text options,"weekly","monthly","yearly". N40 has this
formula: if(N37="monthly",1,"0"). so if "monthly" is the option the
user chooses, i want the command button to be enabled and disabled at
all other times. btw N37 is a merge of three cells for formatting
purposes...i doubt that would make any difference though.

The change to N40 is not triggering the change event. You can either test
N37, like so

Private Sub Worksheet_Change(ByVal Target As Range)

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

End Sub

or add more complex calculate event code.

--

HTH

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


"yoram" wrote in message
oups.com...
ok, cell N37 is data validated with the 'list' condition. the list has
three three text options,"weekly","monthly","yearly". N40 has this
formula: if(N37="monthly",1,"0"). so if "monthly" is the option the
user chooses, i want the command button to be enabled and disabled at
all other times. btw N37 is a merge of three cells for formatting
purposes...i doubt that would make any difference though.