Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Hiding Cells and Buttons (2008)

    Lounger's - I have a spreadsheet that is made up of a number of options buttons in group boxes as well as text in cells.

    Is there a way to hide selected option buttons and cells based on a Yes/No response of a particular option button? - so if "No" is selected they hide and if "Yes" is selected they unhide.

    I hope that makes sense.

    Any thoughts?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hiding Cells and Buttons (2008)

    Option buttons are Shape objects that have a Visible property that can be set to True or False. You'd have to find the names or index numbers of the option buttons.
    For example:

    ActiveSheet.Shapes("OptionButton1").Visible = False

    or

    ActiveSheet.Shapes(4).Visible = True

    You cannot really hide individual cells, only entire rows or columns. But you can set the font color to the same as the background color:

    With ActiveSheet.Range("B5")
    .Font.Color = .Interior.Color
    End With

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Hiding Cells and Buttons (2008)

    Thanks for the quick response - I'll have a play with it and see what happens.

    Another option that I was thinking of was having a say a rectangle shape (with the same color as the background) that would either come to the front or go to the back, based on the Yes/No response of a button.

    Is that possible?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hiding Cells and Buttons (2008)

    A rectangle is a good idea, but you'd be better off hiding and unhiding it using its Visible property, for although you can move a shape behind other shapes, it's always on top of the cells, so you can't send it behind cells.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Cells and Buttons (2008)

    Have you thought about creating a Userform within the VBA Project Explorer. You may have more versatility with a Userform which can be controlled (enabled/disabled) relatively easily.

    Various controls that can be added to the form include:
    labels, textboxes, comboboxes, checkboxes, optionbuttons and etc.


    Regards,
    John

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Hiding Cells and Buttons (2008)

    Thanks Hans - How would I go about hiding and unhiding it using its Visible property - based on a Yes/No response?

    I assume that it would be some VBA code (I'm not good at that).

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hiding Cells and Buttons (2008)

    If the option buttons were created from the Forms toolbar, you'll create a macro in a standard module for each of the buttons. You can assign a macro to an option button from the Forms toolbar through its right-click menu. Here's an example of such macros:

    Sub TurnOff()
    ActiveSheet.Shapes("Rectangle 2").Visible = False
    End Sub

    Sub TurnOn()
    ActiveSheet.Shapes("Rectangle 2").Visible = True
    End Sub

    If the option buttons have been created from the Control Toolbox the situation is different, but from your first post in this thread I assume that you used the Forms toolbar.

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Hiding Cells and Buttons (2008)

    Hans - thanks.

    I assigned the macro as you suggested (I think) but are getting a compile error - I've attached a sample of what I'm try to achieve.

    Would you mind showing me on the attached how the macro should look, I also added to the sheet, how would the macro change if it was based on a "true or False" calculation in a cell.

    Thanks for all your assistance
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hiding Cells and Buttons (2008)

    Your code starts with

    Sub OptionButton2_Click()
    Sub TurnOff()

    You can't have two Sub ... lines without an End Sub in between. The first line should go, it doesn't belong to anything.

    You should replace Rectangle 2 with the actual name of YOUR rectangle. The easiest way to find it is:
    - Click on the edge of the rectangle to select it.
    - Activate the Visual Basic Editor (Alt+F11).
    - Activate the Immediate window (Ctrl+G).
    - Type <code>? Selection.Name</code> and press Enter.
    - You'll see that it is named <code>Rectangle 1</code>
    - So you must change <code>Rectangle 2</code> to <code>Rectangle 1</code> in both macros.

    Next, right-click the first radio button, select Assign Macro... from the popup menu and select TurnOff.
    Then right-click the second radio button, select Assign Macro... from the popup menu and select TurnOn.

    If you want to show/hide the rectangle based on the result of a formula, say in cell B1:
    - Right-click the sheet tab.
    - Type or copy/paste the following code into the sheet module that appears:
    <code>
    Private Sub Worksheet_Calculate()
    Me.Shapes("Rectangle 1").Visible = Me.Range("B1")
    End Sub
    </code>
    In the code module that belongs to a worksheet, Me refers to that sheet.

  10. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Hiding Cells and Buttons (2008)

    Great thanks for you help - will give it a go

    Regards

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Hiding Cells and Buttons (2008)

    I take it you're not actually using Excel 2008 then, since it doesn't support VBA at all! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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