Define a collection of worksheets

Hi,

I am trying to define a collection of worksheets to be used in various modules within vba.

For example

I want to set MyAcoll as sheets ("LISTA","LISTD","LISTF")

I want to set MyBColl as sheets ("LISTB","LISTC","LISTF")

But I want it so that this is defined in one place and then I can refer to it through out my various code modules. I think this will be a big step forward for my coding ability if someone can show me how to do this.

Cheers and happy christmas!

Andy

Something along these lines?

Please Login or Register  to view this content.

For example:

Please Login or Register  to view this content.

It won't help you.
If you try

Please Login or Register  to view this content.

You will experience that the property .visible can't be applied to selected sheets.

The problem is more that the gap between your ambitions and your VBA-abilities is too big. You'd better work on that before asking questions that won't solve your basic question: how to slectively hide/show worksheets.
In another thread you already got the right answer. But you were not able to recognise it as such. Nor were you able to successfully adapt it to your situation or analyse what could be the reason getting unexpected results. My suggestion is to dive more into the basics of VBA before asking for solutions.

Please Login or Register  to view this content.

NB take good care that there is a sheet, called "macro'; if not replace 'macro' in this VBA code by the name of the only sheet you want to be visible.

Originally Posted by snb

My suggestion is to dive more into the basics of VBA before asking for solutions.

Ignoring the (to my mind, anyway) condescending tone of your post for a second, perhaps it would help if you posted code that was comprehensible to most, instead of going out of your way to simply post the shortest and most obfuscated code you can.
You might also like to try responding when people ask you for explanation or clarification of something you post instead of simply ignoring them.
Considering your signature, your berating someone for not recognising that something you posted might answer their question and not using it, is quite astounding!

Hi snb,

It is true that I cannot action .visible to a group of sheets and that would explain the for each sh in sheets, next sh. But my problem is there are some sheets I always want hidden, 1 sheet I want to be displayed alone and 2 sheets I want to be displayed together with no other sheets.

If we say sheets 2,4,6 should always be hidden
Sheet 1 is only displayed alone (when sheets 3 & 5 are hidden)
And sheets 3 & 5 are displayed together (both hidden when sheet 1 is displayed)

So I can say:

Please Login or Register  to view this content.

Which will display all wks except sheet 1

This is a problem as it will display sheets 2,4,6 which should never be visible

Another issue is if I hide sheet 1 and want sheets 3 and 5 visible, I can only refer to sheet 3 or 5 at one time

For example

Please Login or Register  to view this content.

This will then only display sheet 5 and not both 3 and 5 in my tests


For clarification before I joined this forum I couldn't make sense of the most basic of code, now, I still may not be completely fluent but I can fumble my way around vba enough to use other peoples explinations and code examples and adapt them to my situation. From time to time I manage to work things out myself, I may not have the slickest solutions but generally they work eventually. I use the forum where something is completely outside of my current knowledge set in order to learn where I am going wrong and how to deal with a particular problem or function. I am very glad this forum exists and for the patience and help of it members.

snb, I second Romperstomper. If you can't be bothered to explain what your code does then don't berate other members for failing to understand it.
As for lack of knowledge, everyone needs to start somewhere

Originally Posted by mcinnes01

I still may not be completely fluent but I can fumble my way around vba enough to use other peoples explinations and code examples and adapt them to my situation. From time to time I manage to work things out myself, I may not have the slickest solutions but generally they work eventually.

Not many of us are, especially me. Keep learning and we'll hopefully get there one day!!!

Dom

Ask for clarification ? Where ?

Adjust your ambitions to your abilities and strive to the opposite

If you are replying to me, I was talking generally, not about that specific post.

So you must have been referring generally to this post for example ?

No. As much fun as it would be to go back through your posts and pull up some examples, I'm not going to engage in a pointless argument with you. I could simply point out that in that first thread you linked to, you didn't even bother replying.