Workbook Protection VBA code on Hidden Sheets like UserInterfaceOnly

[jaysunice]

I am trying to loosely "secure" a workbook and I feel like there ought to be a better way.

I have a main interaction tab (let's call it sheet Main) where end-users make selections on various drop-downs. However, the drop-down selection/changes drive VBA code that manipulate other sheets (let's call them sheet X and Sheet Y) in the workbook.

The problem: the VBA code cannot run when these other sheets are hidden, therefore, right now I have code, a Protect() and UnProtect() subroutine that run at various points of the code to allow for a pleasant user experience -- one where they don't know that the sheets (X and Y) are being hidden/un-hidden behind the scenes. I feel like this is awkward and the coding is somewhat repetitive.

I could make the workbook such that the sheets X and Y are visible from the get go, but protected, but I'd rather not have the users even see the sheet.

Is there a way to run VBA code that effects hidden sheets without manually un-hiding/re-hiding them each time?

 

[ChrisM]

Most code will run on a hidden sheet. Which bits are failing on you?

 

[EDUCATED MONKEY]

having read your post it gives me the idea that i may be better hiding sheets so other cant screw things up so i to will be looking out for a way to hide sheet of lock column for editing would be another option

 

[jaysunice]

Most code will run on a hidden sheet. Which bits are failing on you?

Learn something every day...you are right. I believe I can get the code to work with sheets still hidden. The statements that were breaking were Sheets("XXXX").Select when really I can more appropriately use Sheets("XXXX").Activate

THANKS!

 

[RoryA]

You almost never need to actually select or activate a sheet in code. Just manipulate it directly.