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.

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.

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.

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.

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.