Hiding and Unhiding two sets of worksheets

My Workbook has 72 worksheets split into two. The first 36 include all the data, but the second 36 have one element removed.
I am very grateful to RoyUK and others who have steered me towards some code that allows me to hide and unhide the first 36 sheets exactly as I want, but when I add the second 36 the code comes up with an error saying the Procedure is too large.
I did want to split the code into two parts activated by two validated drop down menus, but this did not work either.

If I have two drop down menus at D8 and G8 is it possible to have two separate codes as follows

Please Login or Register  to view this content.

The code is OK when I have 36 Worksheets and link this code to one drop down menu. When there are 72 Worksheets it fails. There must be a way to shorten the commands but

Please Login or Register  to view this content.

Does not work whereas

Please Login or Register  to view this content.

does!

This question is linked to my previous post :-

Can I attach a macro to a drop down list selection

Regards

Geoff

Hi,

If you can split your code in two separate macros, then you can try this:

In the ListBox_Click event or ComboBox_Change event (depending on which one you are using as a dropdown), check for the Text property and run the relevant macro accordingly. Here is a sample code:

Please Login or Register  to view this content.

Regards.

Thanks very much for the responses from SE1429 and Martesoft. I was having trouble with a ComboBox so tried MarteSoft's idea and understanding it almost fully implemented it successfully, until I came across the inbuilt limit of 60 arguments for a procedure. Unfortunately I need 72.

Microsoft Help tells me that I can do this with an array "You can also pass multiple arguments by placing them in an array"

My working code is as follows with 36 Worksheets, but I need to add another set of 36 Worksheets in exactly the same format as the first 36.

How can I implement an array to get round the 60 argument limit?

Thanks again for all the ideas.

Please Login or Register  to view this content.

I would go with this approach.

Please Login or Register  to view this content.

Andy,

Thank you so much, that is so much simpler!!

Your code tackles the first 36 of my worksheets, but the next 36 are Main1 to Main8, Main1 being 1 worksheet, Main2 being 2 worksheets etc.

I would also like to have a Case showing all the Sheets, a Case showing all the Mains and a Case hiding all the worksheets.

It just amazes me how many different ways of doing the same thing there are!

Thanks again

Pass the Sheet/Main information as another argument.
And add condition to test for Index of zero and use that to unhide all appropriate sheets.

Please Login or Register  to view this content.

Andy,

Thanks a lot, I'm almost there!! I had to change the last Next to an End If.

My worksheets are now named 1-Sheet1_F through to 8-Sheet8_F and 1-Sheet1_M to 8-Sheet8_M. As a result I have changed the order of the lngIndex and Names in your code.

I can now select any of the M or F drop down menu items and it will display them but it does not hide the F or M sheets previously selected.

If I manually hide all worksheets and then select All-Ms or All-Fs it works as expected.
The problem seems to be in the second Else argument in SheetVisibility Sub. I have tried changing this part but have only managed to make it worse! Can you see what is wrong?

Please Login or Register  to view this content.

This revision should take care of the all _F or _M sheets.

Please Login or Register  to view this content.

With this change to the worksheet event

Please Login or Register  to view this content.

Andy,

Your code is excellent and I mostly understand it, however I would never have got anywhere near this without yours and other peoples help.

In the attached workbook, including your code, you will see that from the Control worksheet if you select All-F, all the worksheets ending with _F are revealed and the same with All-M, all worksheets ending _M are revealed. If having selected All-M you then select 1-F, the only worksheets that should be visible is 1-Sheet_F, but all the worksheets ending _M are still visible.

I tried to include Case "None" by combining your code for All-F and All-M but could not get it to run. This is one bit of the code I do not understand very well, but think it is in the first 2 For in SheetVisibility!

Would it be easier to re-name each of the worksheets so that they are F_1-Sheet1 to F_8-Sheet8 etc?

Regards

Geoff

You need to add another test, this time for the value -1 which will be used to mean hide both Name and Othername sheets.
Also you will need to pass both _F and _M in order to hide all sheets when picking a specific value.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Andy,

That is magnificent!!

Just in case anyone else has been following this thread, attached is the final complete working workbook.

Yet again ExcelForum.com answers all the questions.

Best Regards

Geoff