Protect Worksheets

[astrikor]

I find that by hiding a worksheet to protect it, any macro operation on
the worksheet is also inhibited.
Does anyone know of a way to allow macros to operate on a worksheet
while requiring a password from anyone else?

 

[davesexcel]

*You can unhide the sheets in the macro, and then hide them again, when
the action is completed, you can also protect your sheets by going to
tools,protection, protect sheets, ensure that the cells that need
changing have been unlock before you protect the sheet, *

 

[funkymonkUK]

I find that by hiding a worksheet to protect it, any macro operation on
the worksheet is also inhibited.
Does anyone know of a way to allow macros to operate on a worksheet
while requiring a password from anyone else?

Let me see if I understand you question.

You have a sheet lets say full of data and you dont want anybody to see
it or mess with it. the sheet has macros?

are these standarded recorded macros if not are they attached to the
specific sheet that you want hidden?

I have something similar except I have a password which will run the
macro based on a password. the macro then unhides and unprotects does
its thing then protects and hides.

is this what you looking for?

 

[astrikor]

Thanks funkymonkUK - that it exactly - and your solution works great.
I now have the whole thing password protected, (macros and worksheets)
from interference, with the automacro unhiding and hiding the
worksheets as part of its runtime code.

Many thanks. Astrikor

 

[funkymonkUK]

glad to be of servic

 

[astrikor]

Thanks funkymonkUK - that it exactly - and your solution works great.
I now have the whole thing password protected, (macros and worksheets)
from interference, with the automacro unhiding and hiding the
worksheets as part of its runtime code.

Many thanks. Astrikor

Sorry guys,

It all falls apart when I hit "Esc" while the macro is running - the
macro stops revealing the "hidden" worksheets. This is surprising
because it overrides a previous Application.ScreenUpdating = False
command which normally holds the starting worksheet sheet on the
screen.

It looks as if i need a command to request a password so that when any
keyboard key is pressed while the macro is running NOTHING can change
until password enabled.

Any ideas?
Thanks
Astrikor

 

[funkymonkUK]

how long does you macro run that you got time to press "esc"?

 

[astrikor]

It takes 12 seconds.

Why so long? OK. The macro takes one of a sequence of data strings from
one sheet, pastes it to a second sheet where the string is processed by
predictive algorithms and cell formulae, the results of which are
logged into a third sheet. The macro then returns to the first sheet to
collect the next data string.There are hundreds of data strings, each
containing hundreds of data points.

While all this is happening, I don't want it to be interrupted by
external inputs, nor do I want anything changed (either deliberately or
by mistake).

On the other hand, as I may need to edit something, I don't want to
lock myself out (which happens if I use "Application.Interactive =
False/True" and hit "Esc" during macro operation).

Both the Sheet Hide and the Sheet Protect methods have to be unhided
and unprotected for the macro to be allowed to modify the sheets.
Perhaps what I need is a means of requiring a password when any key is
pressed during macro operation, but which allows the macro to continue
unless the password is correct.

Am I asking too much??

Astrikor

 

[funkymonkUK]

i think you might be asking to much. could you post your code and se
what we can do for you

 

[astrikor]

Its an idea, not sure how that would help, but I am prevented by
confidentiallity unfortunately.

 

[Dave Peterson]

Take a look at application.EnableCancelKey in VBA's help.

 

[astrikor]

Many thanks Dave,
That's the way to do it.
All working fine now.
Astriko