Hiding apreadsheet area on press of radio button

[pleased2help]

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]

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]

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]

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]

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]

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

[pleased2help]

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

[Hercules1946]

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]

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]

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