Multiple With Statements

[Statto]

I'm trying to condense down a list of With statements I have written in a module into a much shorter piece of code. The purpose of the code is to enable/disable the user's ability to select cells and scroll.

This is the code to disable cell selection and scrolling:

Please Login or Register  to view this content.

And this is the code to enable cell selection/scrolling:

Please Login or Register  to view this content.

So what's the best way of condensing both sets of code? I know it's not as simple as just listing the sheet names at the top of a With statement.

[xlnitwit]

Hi,

You can refactor the repeated code into its own routine

Please Login or Register  to view this content.

[Statto]

Hi,

You can refactor the repeated code into its own routine

Please Login or Register  to view this content.

Thanks for your response. However, when I run the top half of the code that disables the cell selection, I get an error message saying 'Compile error: Sub or Function not defined'. It highlights the 'EnableSelection' part.

[davsth]

I think that the sub is missing a name at the top

so

Please Login or Register  to view this content.

[Statto]

I think that the sub is missing a name at the top

so

Please Login or Register  to view this content.

The thing is, this code is part of one Sub. The purpose is to allow for quick switching between admin view and user view in the workbook. This is the whole code. Let's see if we can integrate a solution to fit into this...

Please Login or Register  to view this content.

[xlnitwit]

This part is a separate routine and should be outside (either before or after, or in a different module to) the original routine

Please Login or Register  to view this content.

then you need only replace the relevant sections in your code with the calls to this routine as I posted.

[Statto]

This part is a separate routine and should be outside (either before or after, or in a different module to) the original routine

Please Login or Register  to view this content.

then you need only replace the relevant sections in your code with the calls to this routine as I posted.

That works great — thanks a lot. The only problem I'm having is when I run the code, it locks the checkboxes, radio buttons and spinners in user mode — meaning they can't be clicked on without a dialog box appearing.

What can I add to the code to make sure that these objects are kept unlocked?