I want to write a macro that unprotects a document when the Workbook opens
up. I know the password of the Workbook, but the code I have does not work.
Here's what I have:

Sub ThisWorkbook_Open()
ActiveWorkbook.Unprotect Password:="1234"
End Sub

Hi Hecsan07,

Sub ThisWorkbook_Open()

This should be:

Private Sub Workbook_Open()

ActiveWorkbook.Unprotect Password:="1234"

You can unprotect one or more sheets in the workbook open event; you cannot
unprotect the workbook because, in this case a password is required to open
the file and the workbook open event follows, in response to a valid
password.

Therefore, assuming that you want to unprotect sheet1 in the workbook open
event, try:

Private Sub Workbook_Open()
Me.Sheets("Sheet1").Unprotect Password:="1234"
End Sub


---
Regards,
Norman



"hecsan07" wrote in message
...
I want to write a macro that unprotects a document when the Workbook opens
up. I know the password of the Workbook, but the code I have does not
work.
Here's what I have:

Sub ThisWorkbook_Open()
ActiveWorkbook.Unprotect Password:="1234"
End Sub

Norman,

Thanks for your help. I used the sample code you wrote and wrote some more
to iterate through the sheets in the current workbook and unprotect all of
them. However, I still get the password prompt. That is precisely what I want
to get rid of. I don't want to the password prompt. Here's what ended up with:

Private Sub Workbook_Open()
ProtectAll
End Sub


Sub ProtectAll()
Dim myCount 'This line of code is optional
Dim i 'This line of code is optional

myCount = Application.Sheets.Count
Sheets(1).Select 'This line of code selects the 1st sheet

For i = 1 To myCount
UnProtectSheet i

If i = myCount Then
End
End If

ActiveSheet.Next.Select
Next i
End Sub

Sub UnProtectSheet(i)
Me.Sheets(i).Unprotect Password:="1234"
End Sub

Can you please point me in the direction of my error? I'll truly appreciate
it.

-Hector

"Norman Jones" wrote:

Hi Hecsan07,

Sub ThisWorkbook_Open()

This should be:

Private Sub Workbook_Open()

ActiveWorkbook.Unprotect Password:="1234"

You can unprotect one or more sheets in the workbook open event; you cannot
unprotect the workbook because, in this case a password is required to open
the file and the workbook open event follows, in response to a valid
password.

Therefore, assuming that you want to unprotect sheet1 in the workbook open
event, try:

Private Sub Workbook_Open()
Me.Sheets("Sheet1").Unprotect Password:="1234"
End Sub


---
Regards,
Norman



"hecsan07" wrote in message
...
I want to write a macro that unprotects a document when the Workbook opens
up. I know the password of the Workbook, but the code I have does not
work.
Here's what I have:

Sub ThisWorkbook_Open()
ActiveWorkbook.Unprotect Password:="1234"
End Sub

Hi Hecsan07,

The following worked for me:

'In the workbook's ThisWorkbook Module
'-------------------------------------------

Private Sub Workbook_Open()
Dim SH As Worksheet

For Each SH In Me.Worksheets
SH.Unprotect Password:="1234"
Next SH
End Sub


'In a standard module (not a sheet module, not the Thisworkbook module)
'---------------------------

Option Explicit
Sub ProtectAllSheets()
Dim SH As Worksheet
For Each SH In ThisWorkbook.Worksheets
SH.Protect Password:="1234"
Next SH
End Sub

Sub UnProtectAllSheets()
Dim SH As Worksheet
For Each SH In ThisWorkbook.Worksheets
SH.Unprotect Password:="1234"
Next SH
End Sub

'-------------------------------------------------

The Workbook_Open event is used automatically to unprotect all worksheets.

Rhe two subs in the standard module can be invoked, on demand, either
manually or via VBA, to protect (or unprotect) all worksheets,

In all instances the common password (1234) is used.

Irrespective of which procedure is called, no password prompts should be
experienced.


---
Regards,
Norman



"hecsan07" wrote in message
...
Norman,

Thanks for your help. I used the sample code you wrote and wrote some more
to iterate through the sheets in the current workbook and unprotect all of
them. However, I still get the password prompt. That is precisely what I
want
to get rid of. I don't want to the password prompt. Here's what ended up
with:

Private Sub Workbook_Open()
ProtectAll
End Sub


Sub ProtectAll()
Dim myCount 'This line of code is optional
Dim i 'This line of code is optional

myCount = Application.Sheets.Count
Sheets(1).Select 'This line of code selects the 1st sheet

For i = 1 To myCount
UnProtectSheet i

If i = myCount Then
End
End If

ActiveSheet.Next.Select
Next i
End Sub

Sub UnProtectSheet(i)
Me.Sheets(i).Unprotect Password:="1234"
End Sub

Can you please point me in the direction of my error? I'll truly
appreciate
it.

-Hector

"Norman Jones" wrote:

Hi Hecsan07,

Sub ThisWorkbook_Open()

This should be:

Private Sub Workbook_Open()

ActiveWorkbook.Unprotect Password:="1234"

You can unprotect one or more sheets in the workbook open event; you
cannot
unprotect the workbook because, in this case a password is required to
open
the file and the workbook open event follows, in response to a valid
password.

Therefore, assuming that you want to unprotect sheet1 in the workbook
open
event, try:

Private Sub Workbook_Open()
Me.Sheets("Sheet1").Unprotect Password:="1234"
End Sub


---
Regards,
Norman



"hecsan07" wrote in message
...
I want to write a macro that unprotects a document when the Workbook
opens
up. I know the password of the Workbook, but the code I have does not
work.
Here's what I have:

Sub ThisWorkbook_Open()
ActiveWorkbook.Unprotect Password:="1234"
End Sub

Norman,

Thanks again for your help on this. My problem still persists. I think I
figure out what the problem is. The Workbook_Open event fires after the
prompts for the password appear. The workbook is write-protected and
open-protected. I feel the code works, but it should unprotect the Workbook
before the password prompts appear. I am looking in MSDN for events that fire
before the Open event, but I have not been very successful thus far.
Any ideas? I'll appreciate them.

-Hector

"Norman Jones" wrote:

Hi Hecsan07,

The following worked for me:

'In the workbook's ThisWorkbook Module
'-------------------------------------------

Private Sub Workbook_Open()
Dim SH As Worksheet

For Each SH In Me.Worksheets
SH.Unprotect Password:="1234"
Next SH
End Sub


'In a standard module (not a sheet module, not the Thisworkbook module)
'---------------------------

Option Explicit
Sub ProtectAllSheets()
Dim SH As Worksheet
For Each SH In ThisWorkbook.Worksheets
SH.Protect Password:="1234"
Next SH
End Sub

Sub UnProtectAllSheets()
Dim SH As Worksheet
For Each SH In ThisWorkbook.Worksheets
SH.Unprotect Password:="1234"
Next SH
End Sub

'-------------------------------------------------

The Workbook_Open event is used automatically to unprotect all worksheets.

Rhe two subs in the standard module can be invoked, on demand, either
manually or via VBA, to protect (or unprotect) all worksheets,

In all instances the common password (1234) is used.

Irrespective of which procedure is called, no password prompts should be
experienced.


---
Regards,
Norman



"hecsan07" wrote in message
...
Norman,

Thanks for your help. I used the sample code you wrote and wrote some more
to iterate through the sheets in the current workbook and unprotect all of
them. However, I still get the password prompt. That is precisely what I
want
to get rid of. I don't want to the password prompt. Here's what ended up
with:

Private Sub Workbook_Open()
ProtectAll
End Sub


Sub ProtectAll()
Dim myCount 'This line of code is optional
Dim i 'This line of code is optional

myCount = Application.Sheets.Count
Sheets(1).Select 'This line of code selects the 1st sheet

For i = 1 To myCount
UnProtectSheet i

If i = myCount Then
End
End If

ActiveSheet.Next.Select
Next i
End Sub

Sub UnProtectSheet(i)
Me.Sheets(i).Unprotect Password:="1234"
End Sub

Can you please point me in the direction of my error? I'll truly
appreciate
it.

-Hector

"Norman Jones" wrote:

Hi Hecsan07,

Sub ThisWorkbook_Open()

This should be:

Private Sub Workbook_Open()

ActiveWorkbook.Unprotect Password:="1234"

You can unprotect one or more sheets in the workbook open event; you
cannot
unprotect the workbook because, in this case a password is required to
open
the file and the workbook open event follows, in response to a valid
password.

Therefore, assuming that you want to unprotect sheet1 in the workbook
open
event, try:

Private Sub Workbook_Open()
Me.Sheets("Sheet1").Unprotect Password:="1234"
End Sub


---
Regards,
Norman



"hecsan07" wrote in message
...
I want to write a macro that unprotects a document when the Workbook
opens
up. I know the password of the Workbook, but the code I have does not
work.
Here's what I have:

Sub ThisWorkbook_Open()
ActiveWorkbook.Unprotect Password:="1234"
End Sub

Hi Hector,

I think that we are at cross purposes.

I have been dealing with worksheet protection. I suspect that you refer to
the worlbook protection.

If this interpretation is correct, as pointed out earlier in this thread,
the workbook's access password prompt cannot be handled by any workbook
event code. This is because the event code cannot run until the workbook
opens, and the workbook cannot open until the user provides the password.

If it is the access password which is the problem, an answer might be to
open the problematic workbook from another workbook.

Before proceeding further, however, it seems sensible to agree the scenario.



---
Regards,
Norman



"hecsan07" wrote in message
...
Norman,

Thanks again for your help on this. My problem still persists. I think I
figure out what the problem is. The Workbook_Open event fires after the
prompts for the password appear. The workbook is write-protected and
open-protected. I feel the code works, but it should unprotect the
Workbook
before the password prompts appear. I am looking in MSDN for events that
fire
before the Open event, but I have not been very successful thus far.
Any ideas? I'll appreciate them.

-Hector

"Norman Jones" wrote:

Hi Hecsan07,

The following worked for me:

'In the workbook's ThisWorkbook Module
'-------------------------------------------

Private Sub Workbook_Open()
Dim SH As Worksheet

For Each SH In Me.Worksheets
SH.Unprotect Password:="1234"
Next SH
End Sub


'In a standard module (not a sheet module, not the Thisworkbook module)
'---------------------------

Option Explicit
Sub ProtectAllSheets()
Dim SH As Worksheet
For Each SH In ThisWorkbook.Worksheets
SH.Protect Password:="1234"
Next SH
End Sub

Sub UnProtectAllSheets()
Dim SH As Worksheet
For Each SH In ThisWorkbook.Worksheets
SH.Unprotect Password:="1234"
Next SH
End Sub

'-------------------------------------------------

The Workbook_Open event is used automatically to unprotect all
worksheets.

Rhe two subs in the standard module can be invoked, on demand, either
manually or via VBA, to protect (or unprotect) all worksheets,

In all instances the common password (1234) is used.

Irrespective of which procedure is called, no password prompts should be
experienced.


---
Regards,
Norman



"hecsan07" wrote in message
...
Norman,

Thanks for your help. I used the sample code you wrote and wrote some
more
to iterate through the sheets in the current workbook and unprotect all
of
them. However, I still get the password prompt. That is precisely what
I
want
to get rid of. I don't want to the password prompt. Here's what ended
up
with:

Private Sub Workbook_Open()
ProtectAll
End Sub


Sub ProtectAll()
Dim myCount 'This line of code is optional
Dim i 'This line of code is optional

myCount = Application.Sheets.Count
Sheets(1).Select 'This line of code selects the 1st sheet

For i = 1 To myCount
UnProtectSheet i

If i = myCount Then
End
End If

ActiveSheet.Next.Select
Next i
End Sub

Sub UnProtectSheet(i)
Me.Sheets(i).Unprotect Password:="1234"
End Sub

Can you please point me in the direction of my error? I'll truly
appreciate
it.

-Hector

"Norman Jones" wrote:

Hi Hecsan07,

Sub ThisWorkbook_Open()

This should be:

Private Sub Workbook_Open()

ActiveWorkbook.Unprotect Password:="1234"

You can unprotect one or more sheets in the workbook open event; you
cannot
unprotect the workbook because, in this case a password is required to
open
the file and the workbook open event follows, in response to a valid
password.

Therefore, assuming that you want to unprotect sheet1 in the workbook
open
event, try:

Private Sub Workbook_Open()
Me.Sheets("Sheet1").Unprotect Password:="1234"
End Sub


---
Regards,
Norman



"hecsan07" wrote in message
...
I want to write a macro that unprotects a document when the Workbook
opens
up. I know the password of the Workbook, but the code I have does
not
work.
Here's what I have:

Sub ThisWorkbook_Open()
ActiveWorkbook.Unprotect Password:="1234"
End Sub

Norman

You are absolutely correct. I am sorry for the misunderstanding. The only
protection I see in the Options-Security dialog is for Workbooks. What I
need is, therefore, unprotection for Workbook.
I guess since we can't handle this type of security from the Workbook
itself, the sensible thing is to unprotect the Worksheets in the Workbook
with the previous code you provided for me. It shouldn't really be a problem.

Thanks for all your help.
-Hector

"Norman Jones" wrote:

Hi Hector,

I think that we are at cross purposes.

I have been dealing with worksheet protection. I suspect that you refer to
the worlbook protection.

If this interpretation is correct, as pointed out earlier in this thread,
the workbook's access password prompt cannot be handled by any workbook
event code. This is because the event code cannot run until the workbook
opens, and the workbook cannot open until the user provides the password.

If it is the access password which is the problem, an answer might be to
open the problematic workbook from another workbook.

Before proceeding further, however, it seems sensible to agree the scenario.



---
Regards,
Norman



"hecsan07" wrote in message
...
Norman,

Thanks again for your help on this. My problem still persists. I think I
figure out what the problem is. The Workbook_Open event fires after the
prompts for the password appear. The workbook is write-protected and
open-protected. I feel the code works, but it should unprotect the
Workbook
before the password prompts appear. I am looking in MSDN for events that
fire
before the Open event, but I have not been very successful thus far.
Any ideas? I'll appreciate them.

-Hector

"Norman Jones" wrote:

Hi Hecsan07,

The following worked for me:

'In the workbook's ThisWorkbook Module
'-------------------------------------------

Private Sub Workbook_Open()
Dim SH As Worksheet

For Each SH In Me.Worksheets
SH.Unprotect Password:="1234"
Next SH
End Sub


'In a standard module (not a sheet module, not the Thisworkbook module)
'---------------------------

Option Explicit
Sub ProtectAllSheets()
Dim SH As Worksheet
For Each SH In ThisWorkbook.Worksheets
SH.Protect Password:="1234"
Next SH
End Sub

Sub UnProtectAllSheets()
Dim SH As Worksheet
For Each SH In ThisWorkbook.Worksheets
SH.Unprotect Password:="1234"
Next SH
End Sub

'-------------------------------------------------

The Workbook_Open event is used automatically to unprotect all
worksheets.

Rhe two subs in the standard module can be invoked, on demand, either
manually or via VBA, to protect (or unprotect) all worksheets,

In all instances the common password (1234) is used.

Irrespective of which procedure is called, no password prompts should be
experienced.


---
Regards,
Norman



"hecsan07" wrote in message
...
Norman,

Thanks for your help. I used the sample code you wrote and wrote some
more
to iterate through the sheets in the current workbook and unprotect all
of
them. However, I still get the password prompt. That is precisely what
I
want
to get rid of. I don't want to the password prompt. Here's what ended
up
with:

Private Sub Workbook_Open()
ProtectAll
End Sub


Sub ProtectAll()
Dim myCount 'This line of code is optional
Dim i 'This line of code is optional

myCount = Application.Sheets.Count
Sheets(1).Select 'This line of code selects the 1st sheet

For i = 1 To myCount
UnProtectSheet i

If i = myCount Then
End
End If

ActiveSheet.Next.Select
Next i
End Sub

Sub UnProtectSheet(i)
Me.Sheets(i).Unprotect Password:="1234"
End Sub

Can you please point me in the direction of my error? I'll truly
appreciate
it.

-Hector

"Norman Jones" wrote:

Hi Hecsan07,

Sub ThisWorkbook_Open()

This should be:

Private Sub Workbook_Open()

ActiveWorkbook.Unprotect Password:="1234"

You can unprotect one or more sheets in the workbook open event; you
cannot
unprotect the workbook because, in this case a password is required to
open
the file and the workbook open event follows, in response to a valid
password.

Therefore, assuming that you want to unprotect sheet1 in the workbook
open
event, try:

Private Sub Workbook_Open()
Me.Sheets("Sheet1").Unprotect Password:="1234"
End Sub


---
Regards,
Norman



"hecsan07" wrote in message
...
I want to write a macro that unprotects a document when the Workbook
opens
up. I know the password of the Workbook, but the code I have does
not
work.
Here's what I have:

Sub ThisWorkbook_Open()
ActiveWorkbook.Unprotect Password:="1234"
End Sub

Hi Hector,

If you want to open the acccess protected workbook without receiving a
password prompt, all you need to do is open the book with code from another
workbook.

So, assume that the workbook to be opened is "Book1.xls" ,

in another workbook include code like:

Sub Tester
Workbooks.Open Filename:="Book1 .xls", password:="1234"
End Sub


According to your requirements, this code could be included in the second
(helper) workbook's Open Event code. In this way, opening the helper book
would automatically open the required workbook, and no password prompt would
be observed.


---
Regards,
Norman



"hecsan07" wrote in message
...
Norman

You are absolutely correct. I am sorry for the misunderstanding. The only
protection I see in the Options-Security dialog is for Workbooks. What I
need is, therefore, unprotection for Workbook.
I guess since we can't handle this type of security from the Workbook
itself, the sensible thing is to unprotect the Worksheets in the Workbook
with the previous code you provided for me. It shouldn't really be a
problem.

Thanks for all your help.
-Hector

"Norman Jones" wrote:

Hi Hector,

I think that we are at cross purposes.

I have been dealing with worksheet protection. I suspect that you refer
to
the worlbook protection.

If this interpretation is correct, as pointed out earlier in this thread,
the workbook's access password prompt cannot be handled by any workbook
event code. This is because the event code cannot run until the workbook
opens, and the workbook cannot open until the user provides the password.

If it is the access password which is the problem, an answer might be to
open the problematic workbook from another workbook.

Before proceeding further, however, it seems sensible to agree the
scenario.



---
Regards,
Norman



"hecsan07" wrote in message
...
Norman,

Thanks again for your help on this. My problem still persists. I think
I
figure out what the problem is. The Workbook_Open event fires after the
prompts for the password appear. The workbook is write-protected and
open-protected. I feel the code works, but it should unprotect the
Workbook
before the password prompts appear. I am looking in MSDN for events
that
fire
before the Open event, but I have not been very successful thus far.
Any ideas? I'll appreciate them.

-Hector

"Norman Jones" wrote:

Hi Hecsan07,

The following worked for me:

'In the workbook's ThisWorkbook Module
'-------------------------------------------

Private Sub Workbook_Open()
Dim SH As Worksheet

For Each SH In Me.Worksheets
SH.Unprotect Password:="1234"
Next SH
End Sub


'In a standard module (not a sheet module, not the Thisworkbook
module)
'---------------------------

Option Explicit
Sub ProtectAllSheets()
Dim SH As Worksheet
For Each SH In ThisWorkbook.Worksheets
SH.Protect Password:="1234"
Next SH
End Sub

Sub UnProtectAllSheets()
Dim SH As Worksheet
For Each SH In ThisWorkbook.Worksheets
SH.Unprotect Password:="1234"
Next SH
End Sub

'-------------------------------------------------

The Workbook_Open event is used automatically to unprotect all
worksheets.

Rhe two subs in the standard module can be invoked, on demand, either
manually or via VBA, to protect (or unprotect) all worksheets,

In all instances the common password (1234) is used.

Irrespective of which procedure is called, no password prompts should
be
experienced.


---
Regards,
Norman



"hecsan07" wrote in message
...
Norman,

Thanks for your help. I used the sample code you wrote and wrote
some
more
to iterate through the sheets in the current workbook and unprotect
all
of
them. However, I still get the password prompt. That is precisely
what
I
want
to get rid of. I don't want to the password prompt. Here's what
ended
up
with:

Private Sub Workbook_Open()
ProtectAll
End Sub


Sub ProtectAll()
Dim myCount 'This line of code is optional
Dim i 'This line of code is optional

myCount = Application.Sheets.Count
Sheets(1).Select 'This line of code selects the 1st sheet

For i = 1 To myCount
UnProtectSheet i

If i = myCount Then
End
End If

ActiveSheet.Next.Select
Next i
End Sub

Sub UnProtectSheet(i)
Me.Sheets(i).Unprotect Password:="1234"
End Sub

Can you please point me in the direction of my error? I'll truly
appreciate
it.

-Hector

"Norman Jones" wrote:

Hi Hecsan07,

Sub ThisWorkbook_Open()

This should be:

Private Sub Workbook_Open()

ActiveWorkbook.Unprotect Password:="1234"

You can unprotect one or more sheets in the workbook open event;
you
cannot
unprotect the workbook because, in this case a password is required
to
open
the file and the workbook open event follows, in response to a
valid
password.

Therefore, assuming that you want to unprotect sheet1 in the
workbook
open
event, try:

Private Sub Workbook_Open()
Me.Sheets("Sheet1").Unprotect Password:="1234"
End Sub


---
Regards,
Norman



"hecsan07" wrote in message
...
I want to write a macro that unprotects a document when the
Workbook
opens
up. I know the password of the Workbook, but the code I have does
not
work.
Here's what I have:

Sub ThisWorkbook_Open()
ActiveWorkbook.Unprotect Password:="1234"
End Sub