Hiding/unhiding worksheets with option buttons

[K. Georgiadis]

My apologies if the answer exists somewhere in the Forum, but typing "hiding/unhiding" in the "Search" window brings up hundreds of threads that happen to include the words in whatever context.

I want to install pairs of option buttons on a worksheet that is to function like a "Control Panel." The pairs of option buttons are to function like toggle switches:


Option Button A unhides worksheet "AT" and simultaneously hides worksheet "AB"
Option Button B hides worksheet "AT" and simultaneously unhides hides worksheet "AB," i.e., the reverse of Option Button AI am planning on four (4) pairs of these toggle switches but I am hoping that if I can figure one pair I can figure the rest.

As always, your help is greatly appreciated.

[Bob Phillips]

This is based on Forms toolbar option buttons



Public Sub optButton1_Click()
Worksheets("AT").Visible = xlSheetVisible
Worksheets("AB").Visible = xlSheetHidden
End Sub

Public Sub optButton2_Click()
Worksheets("AT").Visible = xlSheetHidden
Worksheets("AB").Visible = xlSheetVisible
End Sub

[Paul_Hossler]

Suggest unhiding AB before hiding AT, just in case there's only 2 sheet, since there has to be at least one visible workshhet


Public Sub optButton2_Click()
Worksheets("AB").Visible = xlSheetVisible
Worksheets("AT").Visible = xlSheetHidden
End Sub


Paul

[Aussiebear]

If someone wanted to hide /unhide multiple sheets, would it just be a simple case of;

Public Sub optButton2_Click()
Worksheets ("AB").Visible = xlSheetVisible
WorkSheets ("AT","AC","AD").Visible = xlSheetHidden
End Sub


providing the other sheets were named "AC" & "AD".

[mikerickson]

1) Why not use a toggle Button instead of two option buttons? Are you resticted to Forms menu controls?

2) If there are only these two sheets, where is the control? The OP implies a third sheet. (Edit: multiple windows are also implied.)

[Bob Phillips]

Try it and see, and you will see not.

To do that, you need



WorkSheets (Array("AT","AC","AD")).Visible = xlSheetHidden


but I think the rewuirment here is to manage pairs of sheets, several pairs over several option button groupings.

[K. Georgiadis]

I am moving away from a design involving hiding/unhiding entire sheets because I have "quick find" navigation buttons that will not work if the sheets are hidden.

I was going to hide/unhide one or more of four (4) total sheets which would have necessitated the creation of separate navigation sheets for each combination -- just not worth the trouble.

I was going to put the pairs of option buttons in a central "control panel" but a combo box would have been fine also. The only reason why was going to use a "switch" from the Forms toolbar is that I feel a lot less capable with events language than with "if" statements based on a linked cell. :(

In fact, I would be most grateful if you can point me to good tutorial for using the Control Toolbox.

[Aussiebear]

Sorry for hijacking this thread but I had been thinking of the situation where an Admin of a workbook could hide a number of sheets from a user but by optioning a button, the required number of sheets could then become visible after a password validation.

[lucas]

Hi K,
I think it would help us if we knew what you were trying to accomplish. Central control panel....quick find navigation buttons...starts to sound to me like you might be better with a userform solution....how about it are you willing to let us know what you are trying to do in exchange for some ideas?

[Bob Phillips]

or a menu ...

[K. Georgiadis]

Sorry guys, firstly the workbook is huge and secondly it contains proprietary client data, so I have no choice but struggle through this on my own.

In spite of the fancy names,

1) Because of the large number of worksheets, the Navigation Page with "quick-find" buttons is simply a collection of buttons with simple macros assigned to them to take the user to the desired worksheet without having to scroll through dozens of worksheets. Each worksheet then has a button to return the user back to the Navigation page.

2) The workbook contains several sheets that use the same basic assumptions (e.g., gasoline usage, corn price per bushel, price of ethanol for use in gasohol, etc.). The Control Panel is nothing but a page where all these standard assumptions are entered once and from which they flow to the appropriate destination cells so that all sheets use uniform assumptions.

Going back to the Navigation Page (which, as I said, is simply a collection of buttons with macros), if I were to hide a sheet by whatever means, clicking the corresponding "quick-find" button would return a VBA error. I would have to make an alternative Navigation Page pop up that only has buttons for the displayed worksheets. Since there will be several combinations of displayed/hidden sheets, I would also need several Navigation pages to match. I decided that it is too much trouble to use the method of hiding/unhiding worksheets. Instead I am working on the alternative of putting the pairs of data on a single sheet, hiding and unhiding portions of the data with option buttons.

[lucas]

Hi K,
How about something like this where all sheets are hidden except the one selected at a time. Look next to help on the main menu for the word Navigation...

[K. Georgiadis]

I'll take a look. Thanks Lucas.