develop a macro which takes password protection on and off
 

I have pretty complicated processes in Excel that I need other users to carry
out on files that I have password protect on (and need password protect on).
I have developed a macro to carry out the first of those processes, but find
that instead of accepting the password as part of the macro, a dialogue box
comes up for the password. At the end of the macro when the protection is to
go back on, it does not allow for the entry of a password at all- the end
result is the password is removed from the protection after the macro has
run. I have the macro on a separate file which only limited people will have
access to, so I can automate the password and not reduce the security.
Ideally I want the macro to accept the password as part of the actual macro
and not display a dialogue box (as the next macro I need to make will need to
remove several passwords and that could be a pain) and also of course put it
back on with a password at the end.
Has anyone come across this and has a solution in editing the macro, or is
there another way to protect with password that I don't know about? I can't
give "permissions" due to the large number of people that will be editing
portions of these files.

develop a macro which takes password protection on and off
 

Password to open or password to modify?

Sub test()
Dim wkbk As Workbook
Dim x As String
Dim y As String

x = "PwordToOpen"
y = "PwordToModify"

Set wkbk = Workbooks.Open(Filename:="I:\Excel\Pword.xls", _
Password:=x, WriteResPassword:=y)


End Sub


"Gai" wrote:

I have pretty complicated processes in Excel that I need other users to carry
out on files that I have password protect on (and need password protect on).
I have developed a macro to carry out the first of those processes, but find
that instead of accepting the password as part of the macro, a dialogue box
comes up for the password. At the end of the macro when the protection is to
go back on, it does not allow for the entry of a password at all- the end
result is the password is removed from the protection after the macro has
run. I have the macro on a separate file which only limited people will have
access to, so I can automate the password and not reduce the security.
Ideally I want the macro to accept the password as part of the actual macro
and not display a dialogue box (as the next macro I need to make will need to
remove several passwords and that could be a pain) and also of course put it
back on with a password at the end.
Has anyone come across this and has a solution in editing the macro, or is
there another way to protect with password that I don't know about? I can't
give "permissions" due to the large number of people that will be editing
portions of these files.

develop a macro which takes password protection on and off
 

Sorry for the tardy reply- Our security at work decided to no longer allow
access to user groups! I have just got back on line.

Thanks for your help. The password protect is just for the individual
worksheets. I have since found an answer (out of complete desperation) by
using ''public const as string "[password]" and then putting the password
next to each ActiveSheet.Unprotect. This is working although I'm sure there
may be quicker ways- I'm just happy it works.

Thanks for your help- I am filing this aside for when I need that- I also
password protect workbooks.


"JMB" wrote:

Password to open or password to modify?

Sub test()
Dim wkbk As Workbook
Dim x As String
Dim y As String

x = "PwordToOpen"
y = "PwordToModify"

Set wkbk = Workbooks.Open(Filename:="I:\Excel\Pword.xls", _
Password:=x, WriteResPassword:=y)


End Sub


"Gai" wrote:

I have pretty complicated processes in Excel that I need other users to carry
out on files that I have password protect on (and need password protect on).
I have developed a macro to carry out the first of those processes, but find
that instead of accepting the password as part of the macro, a dialogue box
comes up for the password. At the end of the macro when the protection is to
go back on, it does not allow for the entry of a password at all- the end
result is the password is removed from the protection after the macro has
run. I have the macro on a separate file which only limited people will have
access to, so I can automate the password and not reduce the security.
Ideally I want the macro to accept the password as part of the actual macro
and not display a dialogue box (as the next macro I need to make will need to
remove several passwords and that could be a pain) and also of course put it
back on with a password at the end.
Has anyone come across this and has a solution in editing the macro, or is
there another way to protect with password that I don't know about? I can't
give "permissions" due to the large number of people that will be editing
portions of these files.

develop a macro which takes password protection on and off
 

That's pretty much how I would do it. I'm sure by now you are aware the
password is put back pretty much the same way

Activesheet.Protect Pword

where Pword is a public constant. Glad to hear you found the answer.




"Gai" wrote:

Sorry for the tardy reply- Our security at work decided to no longer allow
access to user groups! I have just got back on line.

Thanks for your help. The password protect is just for the individual
worksheets. I have since found an answer (out of complete desperation) by
using ''public const as string "[password]" and then putting the password
next to each ActiveSheet.Unprotect. This is working although I'm sure there
may be quicker ways- I'm just happy it works.

Thanks for your help- I am filing this aside for when I need that- I also
password protect workbooks.


"JMB" wrote:

Password to open or password to modify?

Sub test()
Dim wkbk As Workbook
Dim x As String
Dim y As String

x = "PwordToOpen"
y = "PwordToModify"

Set wkbk = Workbooks.Open(Filename:="I:\Excel\Pword.xls", _
Password:=x, WriteResPassword:=y)


End Sub


"Gai" wrote:

I have pretty complicated processes in Excel that I need other users to carry
out on files that I have password protect on (and need password protect on).
I have developed a macro to carry out the first of those processes, but find
that instead of accepting the password as part of the macro, a dialogue box
comes up for the password. At the end of the macro when the protection is to
go back on, it does not allow for the entry of a password at all- the end
result is the password is removed from the protection after the macro has
run. I have the macro on a separate file which only limited people will have
access to, so I can automate the password and not reduce the security.
Ideally I want the macro to accept the password as part of the actual macro
and not display a dialogue box (as the next macro I need to make will need to
remove several passwords and that could be a pain) and also of course put it
back on with a password at the end.
Has anyone come across this and has a solution in editing the macro, or is
there another way to protect with password that I don't know about? I can't
give "permissions" due to the large number of people that will be editing
portions of these files.

develop a macro which takes password protection on and off
 

This might be what you are looking for...

Sub Sheet_Protect_test()
If ActiveSheet.ProtectContents Then
ActiveSheet.Unprotect password:="passwordHere"
' This is where your code or proceedures go, (an example just below)...
Range("someRangeNamehere").ClearContents
' After proceedures do the following...
ActiveSheet.Protect password:="passwordHere",_
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
Else
' This is where your code or proceedures go, (an example just below)...
Range("someRangeNamehere").ClearContents
End If
End Sub

HTH

Good luck.

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture




In article ,
Gai wrote:

Sorry for the tardy reply- Our security at work decided to no longer allow
access to user groups! I have just got back on line.

Thanks for your help. The password protect is just for the individual
worksheets. I have since found an answer (out of complete desperation) by
using ''public const as string "[password]" and then putting the password
next to each ActiveSheet.Unprotect. This is working although I'm sure there
may be quicker ways- I'm just happy it works.

Thanks for your help- I am filing this aside for when I need that- I also
password protect workbooks.


"JMB" wrote:

Password to open or password to modify?

Sub test()
Dim wkbk As Workbook
Dim x As String
Dim y As String

x = "PwordToOpen"
y = "PwordToModify"

Set wkbk = Workbooks.Open(Filename:="I:\Excel\Pword.xls", _
Password:=x, WriteResPassword:=y)


End Sub


"Gai" wrote:

I have pretty complicated processes in Excel that I need other users to
carry
out on files that I have password protect on (and need password protect
on).
I have developed a macro to carry out the first of those processes, but
find
that instead of accepting the password as part of the macro, a dialogue
box
comes up for the password. At the end of the macro when the protection is
to
go back on, it does not allow for the entry of a password at all- the end
result is the password is removed from the protection after the macro has
run. I have the macro on a separate file which only limited people will
have
access to, so I can automate the password and not reduce the security.
Ideally I want the macro to accept the password as part of the actual
macro
and not display a dialogue box (as the next macro I need to make will
need to
remove several passwords and that could be a pain) and also of course put
it
back on with a password at the end.
Has anyone come across this and has a solution in editing the macro, or
is
there another way to protect with password that I don't know about? I
can't
give "permissions" due to the large number of people that will be editing
portions of these files.