+ Reply to Thread
Results 1 to 14 of 14

[SOLVED] hiding a control depending on a cell's value

  1. #1
    yoram
    Guest

    [SOLVED] hiding a control depending on a cell's value

    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


  2. #2
    Bob Phillips
    Guest

    Re: hiding a control depending on a cell's value

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  3. #3
    yoram
    Guest

    Re: hiding a control depending on a cell's value

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


  4. #4
    Bob Phillips
    Guest

    Re: hiding a control depending on a cell's value

    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" <[email protected]> wrote in message
    news:[email protected]...
    > i tried this and keep getting a "Method 'Buttons' of object
    > '_Worksheet' failed" error...any ideas?
    >




  5. #5
    yoram
    Guest

    Re: hiding a control depending on a cell's value

    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.


  6. #6
    Bob Phillips
    Guest

    Re: hiding a control depending on a 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




  7. #7
    yoram
    Guest

    Re: hiding a control depending on a 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.


  8. #8
    Bob Phillips
    Guest

    Re: hiding a control depending on a cell's value

    Excel 97?

    --

    HTH

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


    "yoram" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




  9. #9
    yoram
    Guest

    Re: hiding a control depending on a cell's value

    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.


  10. #10
    Bob Phillips
    Guest

    Re: hiding a control depending on a cell's value

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




  11. #11
    yoram
    Guest

    Re: hiding a control depending on a cell's value

    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.


  12. #12
    yoram
    Guest

    Re: hiding a control depending on a cell's value

    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.


  13. #13
    Bob Phillips
    Guest

    Re: hiding a control depending on a cell's value



    --

    HTH

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


    "yoram" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




  14. #14
    Bob Phillips
    Guest

    Re: hiding a control depending on a cell's value

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1