Sheet codenames work in array but not named range

[StevePM]

I would be grateful if someone could tell me why this works:

Please Login or Register  to view this content.

but this doesn't (throws "Object doesn't support this property or method"):

Please Login or Register  to view this content.

I am guessing that the array is passing the sheet codenames as objects and that's what is needed. If so, what do I need to do to get the same sheet codenames held in the named range "MainSheets" to work in the same way? I have tried reading "MainSheets" into an array but that doesn't work.

Thanks.

[berlan]

See if this works for you,

Please Login or Register  to view this content.

EDIT: refers to sheet names, will look further into codenames..

[bulina2k]

have you tried

Please Login or Register  to view this content.

?

[mehmetcik]

What is Mainsheets? is that a named range on your spreadsheet?

If so that is a text string not a sheet.

Your code is trying to find a sheet

I use:-

Please Login or Register  to view this content.

[StevePM]

have you tried

Please Login or Register  to view this content.

?

Thanks, but this only works with sheet names not codenames.

[StevePM]

What is Mainsheets? is that a named range on your spreadsheet?

If so that is a text string not a sheet.

Your code is trying to find a sheet

I use:-

Please Login or Register  to view this content.

Thanks for replying. Yes, "MainSheets" is a named range in the workbook, and contains a list of some worksheets by codename I want to set as visible or not visible. As you say, they are strings so it doesn't work. I think they would need to be converted to objects, but I'm not sure VBA can do that.

What I don't understand is that if I put the sheet codenames into an array (eg Array(Sheet01, Sheet02, Sheet03) what I want to do works. Your code only seems to work on sheet names, not code names. The following code fails with the compile error "Invalid qualifier".

Please Login or Register  to view this content.

[StevePM]

EDIT: refers to sheet names, will look further into codenames..

Thanks, that is the challenge! What I don't understand is why putting the sheet codenames into an array (eg Array(Sheet01, Sheet02, Sheet03) works.

[Norie]

Array(Sheet01, Sheet02, Sheet03) is actually an array of sheet objects, not just the names.

How have you defined your named range?

[StevePM]

Further information. As I have said, putting the sheet codenames into an array (eg Array(Sheet01, Sheet02, Sheet03) works. I don't want to do this because several subs need to access a common list which I can edit if things change. So the question really is: how can I maintain only one array several subs can access.

[StevePM]

Thanks for replying.

Array(Sheet01, Sheet02, Sheet03) is actually an array of sheet objects, not just the names.

That's what I suspected. Even that took me some time to get right!

How have you defined your named range?

With name manager (RefersTo: =Workings!$Z$2:$Z$17). The cells themselves are formatted to text. Perhaps there is a cell format that would make it work?

[jindon]

I wouldn't so like this for myself though...

Perhaps,
You need to tick "Trust access to the VBA project object model" in "Macro Settings tab" in "Option".

Please Login or Register  to view this content.

[Norie]

You could create a function than when passed the codenames as strings in an array - which is what I think you have in your named range - it returns an array of sheet objects.

[StevePM]

You need to tick "Trust access to the VBA project object model" in "Macro Settings tab" in "Option".

Please Login or Register  to view this content.

I can't remember why, but I have already ticked "Trust access to the VBA project object model" so your code works!

I wouldn't so like this for myself though...

Why? Is "Trust access to the VBA project object model" dangerous?

Thank you for replying.

[TMS]

A little inelegant but try this:

Please Login or Register  to view this content.

[jindon]

I mean, I would use sheet names not codenames...

[StevePM]

You could create a function than when passed the codenames as strings in an array - which is what I think you have in your named range - it returns an array of sheet objects.

I had a feeling the solution might be something like that. Unfortunately I struggle with functions and arrays so any clues would be gratefully received.

Jindon has suggested the code below that works but it requires "Trust access to the VBA project object model" to be enabled and he/she wouldn't use it themselves, but I don't know why.

Please Login or Register  to view this content.

[StevePM]

I mean, I would use sheet names not codenames...

I understand why, but names might need changing whereas the codename can always remains the same. Thanks for your suggestion though, which works.

[StevePM]

[QUOTE=TMS;4361664]A little inelegant ...

...but it works!

I was thinking comparing the named range to ThisWorkbook.Sheets was the way forward, but kept going down blind alleys. Thank you very much.

[TMS]

You're welcome. Thanks for the rep.

[jindon]

Thanks for the rep.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Mandeep Baluja]

Please Login or Register  to view this content.

[TMS]

@Mandeep Baluja: Late entry but that only works for sheet (tab) names, not Sheet CodeNames.