hiding a control depending on a cell's value

[yoram]

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").Enabled = True
Else
Worksheets("Sheet1").CommandButton("button1").Enabled = False
End If
End Sub

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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.

 

[Bob Phillips]

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]

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.

 

[Bob Phillips]

Excel 97?

--

HTH

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

 

[yoram]

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.

 

[Bob Phillips]

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]

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.

 

[yoram]

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.

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

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)