Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Hiding apreadsheet area on press of radio button

  1. #1

    Hiding apreadsheet area on press of radio button



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    is it possable to hide a specific spreadsheet area with a press of a radio button?
    ie an area L8:L11 to T8:T11 (The area is rectangular)
    Excel 2010
    many Thanks in advance

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    I think that you can only hide complete rows or columns, as anything else would leave you confused about the cell references. You can hide the cell contents by setting the text colour the same as the background,
    if thats any help. Why do you need to hide the cells ?

  3. #3
    The reason behind this a i have a spreadsheet that you select via drop down lists :- a lenghth of material, width of material type of material & tooth pitch required. Depending on the selections it calculates the manufactured cost + margins for resale. It also displays the actual manufactured cost, the margins achieved its the latter i would like to hide in the rectangular boxes as some time a customer comes in and my boss wants the sell price with all the dropdown lists so the customer cant see our actual cost and margins.
    Is it possible two have a radio button that toggles between two sheets one with and one with out the extra data
    When the workbook is opened i only have a front sheet all headings etc are hidden by vba code.

  4. #4
    Good afternoon,

    I don't have the answer but I may have a suggestion. If you set the format to ";;;;" it will be invisible. That could be worked into a macro, and probably linked to a radio button.

    Hope this helps,

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    How about if you keep the full dataset in (say) Sheet2 which is hidden unless you activate it ? Then, (say) Sheet1 could be used when you have customers around.
    This bit of VBA code will hide or make visible Sheet2 and can be assigned to a button or Ctrl + Shift Key combination:

    Sub Macro1()
    If Sheet2.Visible = xlSheetVisible Then
    Sheet2.Visible = xlSheetHidden
    ElseIf Sheet2.Visible = xlSheetHidden Then
    Sheet2.Visible = xlSheetVisible
    End If
    End Sub

    Hit ALT+F11 to open the VB Editor, and copy it into a code module.

    Hope that works for you

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    You could just put a command button on the sheet and use this code

    Private Sub CommandButton1_Click()

    With Range("L8:T11").Font
    If .ColorIndex = 1 Then
    .ColorIndex = 2
    CommandButton1.Caption = "Show Stuff"
    Else
    .ColorIndex = 1
    CommandButton1.Caption = "Hide Stuff"
    End If
    End With

    End Sub

  7. #7
    Thank you for your replies
    I will give them a go on Monday

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NoS View Post
    You could just put a command button on the sheet and use this code

    Private Sub CommandButton1_Click()

    With Range("L8:T11").Font
    If .ColorIndex = 1 Then
    .ColorIndex = 2
    CommandButton1.Caption = "Show Stuff"
    Else
    .ColorIndex = 1
    CommandButton1.Caption = "Hide Stuff"
    End If
    End With

    End Sub
    Correct me if Im wrong, but would this only work if all the text was black ?

  9. #9
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Correct me if Im wrong, but would this only work if all the text was black ?
    Yes, and cell fill color has to be white, but isn't that what Excel's defaults are? Is on my computer.

  10. #10
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NoS View Post
    Yes, and cell fill color has to be white, but isn't that what Excel's defaults are? Is on my computer.
    Hello NoS
    My point was if the spreadsheet was formatted with additional background and font colours in the cells concerned, it might not work. I think that in a majority "black and white" situation, its a very good suggestion.

    Hercules

Page 1 of 2 1 2 LastLast

Posting Permissions

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