Hiding apreadsheet area on press of radio button

P

pleased2help

New member
Joined
Jun 27, 2013
Messages
28
Reaction score
0
Points
0
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
 
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 ?
 
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.
 
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,
 
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
 
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
 
NoS said:
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
Click to expand...

Correct me if Im wrong, but would this only work if all the text was black ?
 
Correct me if Im wrong, but would this only work if all the text was black ?
Click to expand...

Yes, and cell fill color has to be white, but isn't that what Excel's defaults are? Is on my computer.
 
NoS said:
Yes, and cell fill color has to be white, but isn't that what Excel's defaults are? Is on my computer.
Click to expand...

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
 
My point was if the spreadsheet was formatted with additional background and font colours in the cells concerned, it might not work.
Click to expand...

Very true Herc, just an idea that can be easily adapted to toggle text color for what ever the OP actually has.

When dealing with colored text and backgrounds here's a site I frequent to find Excel's color index numbers.
 
NoS said:
Very true Herc, just an idea that can be easily adapted to toggle text color for what ever the OP actually has.

When dealing with colored text and backgrounds here's a site I frequent to find Excel's color index numbers.
Click to expand...

Ive had a look at the link, and its a great information source. Thanks a lot! :)
 
Nos,
That works well, setting the ColorIndex to suit the text and colour fill to suit.

Can that single press Command button be used for two independent areas.
ie range L8:T11 with Color index of 1 and fill color index 15
and range L21:T23 with color index 51 and fill color index 36

I have tried altering your sub to :-

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

End Sub

but that changes all the range from L8:T23 and not just the the two areas.

Many thanks
 
Try it this way
Code: 
Private Sub CommandButton1_Click()

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

End Sub
 
I've past my allowable time to edit previous post but...

you could just change ("L8:T11", "L21:T23") to ("L8:T11, L21:T23")
 
You must log in or register to reply here.
Back
Top