Calling a Sub within a worksheet qualified with a variable of type Worksheet

I have a series of worksheets that each have a Sub declared that set the protection appropriately for that sheet:

Please Login or Register  to view this content.

I thought I would be able to look through all sheets like this:

Please Login or Register  to view this content.

However, this fails to compile because ProtectMe is not an attribute of the type Worksheet (apparently binding for Sub calls is required at compile time).

I am trying to work around this. One approach is brute force, just call each individual worksheet. I could call Protect from a central place instead of using a ProtectMe Sub. Is there any way to do this and keep my (more elegant and flexible) approach? I was also thinking of using classes somehow, but haven't pursued that very far.

Try:

Please Login or Register  to view this content.

Originally Posted by StephenR

I don't really understand, your example was not tailored to individual sheets. Why not just loop through each sheet and apply protection as required?

Each sheet has its own version of ProtectMe in its own module, and each version is called in a loop from another module. I am sorry I was not clear that the ProtectMe code is found in each sheet's module (though it's implementation can be different for each).

In the meantime I have discovered that this works:

Please Login or Register  to view this content.

The reason that I prefer this solution is that I can add or remove sheets, tailor the code in each particular sheet, and not have to change the code shown here. That is, the loop doesn't care how many sheets there are, what their names are, or what their protection should be. It just asks each sheet to set its own protection as desired. Although it is not OOP, strictly speaking, I am treating each sheet as an instance of a class with a method ProtectMe.

Thank you OnErrorGoto0 for your suggestion, I believe that will work. I will give it a try, though as I mentioned I have a workaround.

Yes - same principle

Please Login or Register  to view this content.

returns an Object, hence no issues with the compiler.