Worksheet Protection
 

I have a Workbook in which I want the Worksheets to be protected. Everything
is done with MACROS, and the MACROS both Unprotect and Protect the
Worksheets. I manually Protect the Worksheet and save it. Then I retrieve
it and run one of the MACROS. It asks for the PSW going in, but not coming
out. Then I save it and go thru the process again. This time too, the
Worksheet is Protected, but when I run a MACRO it doesn't ask for a PSW. Nor
does it ask for the PSW if I Unprotect it manually.

Worksheet Protection
 

Sounds like you are omitting the password argument when the macros
protect/unprotect the worksheet.

If you manually set a password then use code to unprotect the sheet without
specifying the password (in your code)

Sheet1.Unprotect

you will still get the dialog box to input a password. When the sheet is
protected, you'll need to include the password with your code

Sheet1.Protect password:="your_password"


"TomD" wrote:

I have a Workbook in which I want the Worksheets to be protected. Everything
is done with MACROS, and the MACROS both Unprotect and Protect the
Worksheets. I manually Protect the Worksheet and save it. Then I retrieve
it and run one of the MACROS. It asks for the PSW going in, but not coming
out. Then I save it and go thru the process again. This time too, the
Worksheet is Protected, but when I run a MACRO it doesn't ask for a PSW. Nor
does it ask for the PSW if I Unprotect it manually.

Worksheet Protection
 

Hi JMB,

Thanks for the reply. Gosh, that's not too good to have the PSW hard coded.
That means that anybody can run the MACROS and make changes to the data.
Isn't there any way via MACROS that the PSW can be a manual function?

TomD


"JMB" wrote:

Sounds like you are omitting the password argument when the macros
protect/unprotect the worksheet.

If you manually set a password then use code to unprotect the sheet without
specifying the password (in your code)

Sheet1.Unprotect

you will still get the dialog box to input a password. When the sheet is
protected, you'll need to include the password with your code

Sheet1.Protect password:="your_password"


"TomD" wrote:

I have a Workbook in which I want the Worksheets to be protected. Everything
is done with MACROS, and the MACROS both Unprotect and Protect the
Worksheets. I manually Protect the Worksheet and save it. Then I retrieve
it and run one of the MACROS. It asks for the PSW going in, but not coming
out. Then I save it and go thru the process again. This time too, the
Worksheet is Protected, but when I run a MACRO it doesn't ask for a PSW. Nor
does it ask for the PSW if I Unprotect it manually.

Worksheet Protection
 

You can get the password from the user via an Inputbox or a userform. I was
thinking along the lines of:

Sub Test()
Dim PSW As String

On Error GoTo CleanUp
PSW = InputBox("Enter Password")
Sheet1.Unprotect password:=PSW

'Do some stuff

Sheet1.Protect password:=PSW

CleanUp:
If Err.Number < 0 Then _
MsgBox "Error " & Err.Number & _
": " & Err.Description

End Sub


A userform would allow you to use a textbox, which has a passwordchar
property that allows you to display a particular character (such as "*")
instead of displaying what the user is typing in the textbox.

If the unprotect and protect statements are not in the same sub, you could
set PSW up as a global variable (see VBA help to learn more about a variables
"Scope").

A word of caution if you have sensitive data - worksheet protection can
easily be broken w/ a macro. Most average users may not figure it out (or
care to even try), but just don't be fooled into thinking your data is safe
from prying eyes.

Read more he
http://www.mcgimpsey.com/excel/removepwords.html


"TomD" wrote:

Hi JMB,

Thanks for the reply. Gosh, that's not too good to have the PSW hard coded.
That means that anybody can run the MACROS and make changes to the data.
Isn't there any way via MACROS that the PSW can be a manual function?

TomD


"JMB" wrote:

Sounds like you are omitting the password argument when the macros
protect/unprotect the worksheet.

If you manually set a password then use code to unprotect the sheet without
specifying the password (in your code)

Sheet1.Unprotect

you will still get the dialog box to input a password. When the sheet is
protected, you'll need to include the password with your code

Sheet1.Protect password:="your_password"


"TomD" wrote:

I have a Workbook in which I want the Worksheets to be protected. Everything
is done with MACROS, and the MACROS both Unprotect and Protect the
Worksheets. I manually Protect the Worksheet and save it. Then I retrieve
it and run one of the MACROS. It asks for the PSW going in, but not coming
out. Then I save it and go thru the process again. This time too, the
Worksheet is Protected, but when I run a MACRO it doesn't ask for a PSW. Nor
does it ask for the PSW if I Unprotect it manually.

Worksheet Protection
 

Thanks for the info. I will give it a try and let you know. Looks good!

"JMB" wrote:

You can get the password from the user via an Inputbox or a userform. I was
thinking along the lines of:

Sub Test()
Dim PSW As String

On Error GoTo CleanUp
PSW = InputBox("Enter Password")
Sheet1.Unprotect password:=PSW

'Do some stuff

Sheet1.Protect password:=PSW

CleanUp:
If Err.Number < 0 Then _
MsgBox "Error " & Err.Number & _
": " & Err.Description

End Sub


A userform would allow you to use a textbox, which has a passwordchar
property that allows you to display a particular character (such as "*")
instead of displaying what the user is typing in the textbox.

If the unprotect and protect statements are not in the same sub, you could
set PSW up as a global variable (see VBA help to learn more about a variables
"Scope").

A word of caution if you have sensitive data - worksheet protection can
easily be broken w/ a macro. Most average users may not figure it out (or
care to even try), but just don't be fooled into thinking your data is safe
from prying eyes.

Read more he
http://www.mcgimpsey.com/excel/removepwords.html


"TomD" wrote:

Hi JMB,

Thanks for the reply. Gosh, that's not too good to have the PSW hard coded.
That means that anybody can run the MACROS and make changes to the data.
Isn't there any way via MACROS that the PSW can be a manual function?

TomD


"JMB" wrote:

Sounds like you are omitting the password argument when the macros
protect/unprotect the worksheet.

If you manually set a password then use code to unprotect the sheet without
specifying the password (in your code)

Sheet1.Unprotect

you will still get the dialog box to input a password. When the sheet is
protected, you'll need to include the password with your code

Sheet1.Protect password:="your_password"


"TomD" wrote:

I have a Workbook in which I want the Worksheets to be protected. Everything
is done with MACROS, and the MACROS both Unprotect and Protect the
Worksheets. I manually Protect the Worksheet and save it. Then I retrieve
it and run one of the MACROS. It asks for the PSW going in, but not coming
out. Then I save it and go thru the process again. This time too, the
Worksheet is Protected, but when I run a MACRO it doesn't ask for a PSW. Nor
does it ask for the PSW if I Unprotect it manually.