Hiding apreadsheet area on press of radio button

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
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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 ?
 

pleased2help

New member
Joined
Jun 27, 2013
Messages
28
Reaction score
0
Points
0
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.
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
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,
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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 ?
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
My point was if the spreadsheet was formatted with additional background and font colours in the cells concerned, it might not work.

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.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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.

Ive had a look at the link, and its a great information source. Thanks a lot! :)
 

pleased2help

New member
Joined
Jun 27, 2013
Messages
28
Reaction score
0
Points
0
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
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
I've past my allowable time to edit previous post but...

you could just change ("L8:T11", "L21:T23") to ("L8:T11, L21:T23")
 
Top