Using Check Boxes, make only one selectable

[zhunter71]

I have never used Check Boxes before and i not sure how to achieve what i need, or even if you can

On the attached, what i would like is for only one of the two boxes to be selectable, so when someone chooses one the other one is greyed out or maybe obscured?

And whichever one is selected it will choose which tabs i have along the bottom to be visible (which i have removed on this sheet)

But lets say for example if someone chooses the top box then tabs named 1, 2 and 3 are to be visible, 4, 5 and 6 are to be hidden

and vice versa so if the second box is selected, 1, 2 and 3 to be hidden, and 4, 5 and 6 to be visible?

but on opening the excel doc i obviously need it to be reset so both boxes are visible and selectable and all tabs are visible

is this possible to do with check boxes please? any help much appreciated! thanks!

[6StringJazzer]

The textbook method for this is to use option buttons instead of checkboxes, which has this feature built in. I have created the option buttons and also the code. Unfortunately, I couldn't put in the code that hides and shows sheets since you deleted the sheets. The code is in the sheet module for Guide.

[zhunter71]

i like the buttons, they work a treat! I am not sure what you mean by "sheet module" though?

I've attached it again with four tabs on, could you add the code so one button hides two, shows two please then i can duplicate that to include other sheets?

and where do i access the control functions which is what each button does when pressed? i have looked under the Developer tab and the Data tab and cant see where to find it?

many thanks!

[6StringJazzer]

Each worksheet has a VBA code module. You already have code in the modules for "Item A - Fabrication" and "Item B - Fabrication" so I am sure you know how this works--maybe we just have a terminology block. I added code in the module for "Guide". This code controls what each button does when pressed.

could you add the code so one button hides two, shows two

You have two sheets for Item A, and two sheets for Item B. Which two do you want to hide/show for which button?

Right now, the code I wrote just shows a message box when you click a button. If you want to show/hide sheets, you use code that looks like this:

Please Login or Register  to view this content.

To make everything visible at startup, put this code into the module ThisWorkbook:

Please Login or Register  to view this content.

[zhunter71]

thank you once again :D

I am trying to work out how you have done this so i can do this on other worksheets,

i have gone to the Developer tab, on attachment Q1, selected the radio button under Active X objects

then on attachment Q2 i have changed the text alongside the buttons (putting a 1 on the end to distinguish it from the ones you did)

but then on Q3 i have gone to View Code and all that has appeared for two new buttons is one line of text highlighted in red, which still says the generic "OptionButton1",

how have you added two buttons and got two entries in the Code area? (the entries in yellow) and how did you change the names of each button so that appears in the code area and not OptionButton1, OptionButton2, etc

many thanks and i am sorry if it seems like they are daft questions, its well out of my comfort zone this, i use Autocad so am more graphics than formula orientated! !

PS i did also try right clicking onto the sheet you did to copy the buttons but the right click didnt work over the buttons for some reason, even though Sheet protect wasnt enabled

[zhunter71]

further to the last post, i have had another go at it and come up with this,
i seem to now have two buttons, which i think i have renamed to match the names you used,
but the same code that works in the sheet you did, doesnt work on this one,
if you could please tell me why this one attached doesnt work, what i am missing
or not doing that should enable me then to add some buttons to other sheets :D many thanks!!

[6StringJazzer]

ActiveX controls work a little differently than Forms controls. They are newer (but not at all new). I generally prefer them because there are more powerful features available for coding their behavior.

To manage an ActiveX control, click the Design button on the ribbon. That will put you into Design mode so that when you click on the control it will not run a macro, you manage the control. If you right-click on the control, you will get a menu; click Properties. This lists all the properties of the control.

ActiveX controls have the properties Name and a Caption, and by default they are both the same when you create the control. This may be confusing to beginners. The default for the first option button is is that Name and Caption are both OptionButton1. If you change the Name, this will change how the control is referenced in code. If you change the Caption, this will change only the label that the user sees on the worksheet. You can also change the Caption by directly editing the control in your worksheet.

If you are in Design mode and double-click on the control, VBA will automatically generate a skeleton for the code that will be run when you click the control. It will use whatever is the Name of the control at the time. The Name of your control is still OptionButton1 even though you changed the Caption.

Now, as to your specific file, when you say "work on other worksheets", do you mean that you want to put buttons other worksheets and have them do the same thing? Or that you want to add functionality for hiding and showing other worksheets?

I looked at your latest attachment and I still do not know specifically what you want them to do--you mentioned hiding and showing sheets but I don't know which sheets are to be hidden in which conditions. Can you specify in detail exactly what you want to happen when you click on "Up And Onto" and "Up And Over"?

[zhunter71]

thanks very much for the explanation.!

On the test example i attached what i have set it to do is, when you click Up and Onto the sheet "Item B - Type M Fabrication" should be hidden

and when you click the Up and Over button these two sheets should become hidden "Item A - BZP Parts List", and "Item A - Fabrication" but neither button makes any sheet become hidden, so i dont know what i have not done right

[6StringJazzer]

You renamed the buttons. The code is looking for buttons named

OptionButtonUpAndOnto
OptionButtonUpAndOver

Please Login or Register  to view this content.

but the actual names of the buttons are

UpAndOnto
UpAndOver

To fix this, change either the names of the buttons, or the code for sheet Guide so they match.

[zhunter71]

oh duh! thanks! i thought the "OptionButton" was part of the necessary code :D I should be good to go now! thanks so much all the help!!!

[6StringJazzer]

Come back any time if you have questions!!