Conditional unprotect and unlocking of cell range
 

I have a series of worksheets.

1: I have all worksheets hidden, except for a worksheet that says macros
must be anabled to properly use the spreadsheet. (It is set up as a

The intent is:

A: if macros are disabled- this is the only worksheet visible.
B: if macros are enabled- this worksheet is hidden and others are made
visible.

C: if the current filename is "original.xls", the first worksheet "request"
is unprotected and cell range C4-E7 is unlocked, then the spreadsheet is
re-protected.

D: if the filename is not "original.xls", cell C4-E7 remains locked in the
worksheet called "request".

This spreadsheet is set up as a form for users to fill out information and
keep track of data in subsequent attached worksheets. The intent is to keep
users from recycling old forms and simply making modifications- because they
make many mistakes if they do not start from a blank set of forms. This
activity, while the user thinks it saves lots of money- it ends up costing
the company thousands of dollars by these users trying to save a few minutes
of time. This above process will keep them from changing customer name,
case and project number- which essentially keeps them from recycling the
forms. The backdoor of hiding the pages and "must enable macros" to use
spreadsheet is to keep them from overriding the formulas... (none of the
users are smart enough to circumvent this approach.

Thanks


How do I go about coding this? I haven't found the resources yet to figure
this out on my own.

Thanks

Conditional unprotect and unlocking of cell range
 

Hi
you may use the workbook event workbook_open()
for this. Place your code to check the name and unhide the sheets in
this procedure. so something like (untested)
sub workbook_open()
dim wks
application.screenupdating=false
for each wks in me.worksheets
wks.visible=true
next
me.worksheets("intro_sheet").visible=false
set wks=me.worksheets("request")
if lcase (me.name)="original.xls" then
wks.unprotect password:="your_password"
wks.range("C4:E7").locked=false
wks.protect password:="your_password"
end if
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


Anon y mous wrote:
I have a series of worksheets.

1: I have all worksheets hidden, except for a worksheet that says
macros must be anabled to properly use the spreadsheet. (It is set
up as a

The intent is:

A: if macros are disabled- this is the only worksheet visible.
B: if macros are enabled- this worksheet is hidden and others are
made visible.

C: if the current filename is "original.xls", the first worksheet
"request" is unprotected and cell range C4-E7 is unlocked, then the
spreadsheet is re-protected.

D: if the filename is not "original.xls", cell C4-E7 remains locked
in the worksheet called "request".

This spreadsheet is set up as a form for users to fill out
information and keep track of data in subsequent attached worksheets.
The intent is to keep users from recycling old forms and simply
making modifications- because they make many mistakes if they do not
start from a blank set of forms. This activity, while the user
thinks it saves lots of money- it ends up costing the company
thousands of dollars by these users trying to save a few minutes of
time. This above process will keep them from changing customer name,
case and project number- which essentially keeps them from recycling
the forms. The backdoor of hiding the pages and "must enable macros"
to use spreadsheet is to keep them from overriding the formulas...
(none of the users are smart enough to circumvent this approach.

Thanks


How do I go about coding this? I haven't found the resources yet to
figure this out on my own.

Thanks

Conditional unprotect and unlocking of cell range
 

For points A&B, assuming the sheet in question is called "Enable Macros" you
need to put some code in the workbook_beforeclose event like this:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myws As Worksheet
For Each myws In Me.Worksheets
If myws.Name < "EnableMacros" Then
myws.Visible = xlSheetHidden
Else
myws.Visible = xlSheetVisible
End If
Next
End Sub

and then in the workbook_open event:

Private Sub Workbook_Open()
Dim myws As Worksheet
For Each myws In Me.Worksheets
If myws.Name = "EnableMacros" Then
myws.Visible = xlSheetHidden
Else
myws.Visible = xlSheetVisible
End If
Next

End Sub

remember the open event will only run if macros are enabled therefore the
Enable Macros sheet will be visible

--
If I've mis-understood the question please tell me.

HTH

ijb

Replies to group please

Not MCSD, MVP, TLA, P&P, PCMCIA, etc just trying to help


"Anon y mous" wrote in message
news:Mkhzc.28202$2i5.10892@attbi_s52...
I have a series of worksheets.

1: I have all worksheets hidden, except for a worksheet that says macros
must be anabled to properly use the spreadsheet. (It is set up as a

The intent is:

A: if macros are disabled- this is the only worksheet visible.
B: if macros are enabled- this worksheet is hidden and others are made
visible.

C: if the current filename is "original.xls", the first worksheet
"request"
is unprotected and cell range C4-E7 is unlocked, then the spreadsheet is
re-protected.

D: if the filename is not "original.xls", cell C4-E7 remains locked in
the
worksheet called "request".

This spreadsheet is set up as a form for users to fill out information and
keep track of data in subsequent attached worksheets. The intent is to
keep
users from recycling old forms and simply making modifications- because
they
make many mistakes if they do not start from a blank set of forms. This
activity, while the user thinks it saves lots of money- it ends up costing
the company thousands of dollars by these users trying to save a few
minutes
of time. This above process will keep them from changing customer name,
case and project number- which essentially keeps them from recycling the
forms. The backdoor of hiding the pages and "must enable macros" to use
spreadsheet is to keep them from overriding the formulas... (none of the
users are smart enough to circumvent this approach.

Thanks


How do I go about coding this? I haven't found the resources yet to
figure
this out on my own.

Thanks

Conditional unprotect and unlocking of cell range
 

Hi,
Thanks for the help! First part works great at revealing active worksheets
when macro is enabled- yet leaves the "must enable macros" sheet as the only
sheet if macros are turned off.


The code:

Private Sub Workbook_Open()
Dim wks
Application.ScreenUpdating = False
For Each wks In Me.Worksheets
wks.Visible = True
Next
Me.Worksheets("intro").Visible = False
Set wks = Me.Worksheets("request") ' name of worksheet =
'request'
If LCase(Me.Name) = "Request03.xls" Then ' name of file
= "Request03.xls
wks.Unprotect password:="mypassword"
wks.Range("B4:J9").Locked = False
wks.Protect password:="mypassword"
End If

End Sub


The first bit works like a charm. If I disable the macro, the four
worksheets: "request", "data", "diagram" and "process" remain hidden and
"Intro" remains visible (which simply says spreadsheet works when you
enable the macro)

However, the spreadsheet does not seem to unlock fields. I start with the
fields locked and protected as statup default, with first use, (conditional
with actual cell filename).. cells B4:J9 remain locked when the file has the
filename "Request03.xls"........(in these cells, B4, C4 and D4 have been
merged, if that makes any difference, likewise, B5, C5, D5 have been
merged, as well as rows 6, 7,8, and 9)


Any ideas??

Thanks much.
Paul





"Frank Kabel" wrote in message
...
Hi
you may use the workbook event workbook_open()
for this. Place your code to check the name and unhide the sheets in
this procedure. so something like (untested)
sub workbook_open()
dim wks
application.screenupdating=false
for each wks in me.worksheets
wks.visible=true
next
me.worksheets("intro_sheet").visible=false
set wks=me.worksheets("request")
if lcase (me.name)="original.xls" then
wks.unprotect password:="your_password"
wks.range("C4:E7").locked=false
wks.protect password:="your_password"
end if
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


Anon y mous wrote:
I have a series of worksheets.

1: I have all worksheets hidden, except for a worksheet that says
macros must be anabled to properly use the spreadsheet. (It is set
up as a

The intent is:

A: if macros are disabled- this is the only worksheet visible.
B: if macros are enabled- this worksheet is hidden and others are
made visible.

C: if the current filename is "original.xls", the first worksheet
"request" is unprotected and cell range C4-E7 is unlocked, then the
spreadsheet is re-protected.

D: if the filename is not "original.xls", cell C4-E7 remains locked
in the worksheet called "request".

This spreadsheet is set up as a form for users to fill out
information and keep track of data in subsequent attached worksheets.
The intent is to keep users from recycling old forms and simply
making modifications- because they make many mistakes if they do not
start from a blank set of forms. This activity, while the user
thinks it saves lots of money- it ends up costing the company
thousands of dollars by these users trying to save a few minutes of
time. This above process will keep them from changing customer name,
case and project number- which essentially keeps them from recycling
the forms. The backdoor of hiding the pages and "must enable macros"
to use spreadsheet is to keep them from overriding the formulas...
(none of the users are smart enough to circumvent this approach.

Thanks


How do I go about coding this? I haven't found the resources yet to
figure this out on my own.

Thanks

unprotect, lock and reprotect file does not seem to work
 

I tried this code, and it does not seem to unprotect, lock and reprotect the
worksheet as indicated.. The cells remain unlocked after saving the file
with a different name, so input is still allowed into the cell range
mentioned- B4 to J9
Any ideas one what I might be doing wrong?

Thanks


"Anon y mous" wrote in message
news:6_NBc.72989$Hg2.53456@attbi_s04...


The code:

Private Sub Workbook_Open()
Dim wks
Application.ScreenUpdating = False
For Each wks In Me.Worksheets
wks.Visible = True
Next
Me.Worksheets("intro").Visible = False
Set wks = Me.Worksheets("request") ' name of worksheet
= 'request'
If LCase(Me.Name) = "Request03.xls" Then ' name of
file = "Request03.xls"
wks.Unprotect password:="mypassword"
wks.Range("B4:J9").Locked = False
wks.Protect password:="mypassword"
End If

End Sub


The first bit works like a charm. If I disable the macro, the four
worksheets: "request", "data", "diagram" and "process" remain hidden and
"Intro" remains visible (which simply says spreadsheet works when you
enable the macro)

However, the spreadsheet does not seem to unlock fields. I start with
the
fields locked and protected as statup default, with first use,
(conditional
with actual cell filename).. cells B4:J9 remain locked when the file has
the
filename "Request03.xls"........(in these cells, B4, C4 and D4 have been
merged, if that makes any difference, likewise, B5, C5, D5 have been
merged, as well as rows 6, 7,8, and 9)


Any ideas??

Thanks much.
Paul





"Frank Kabel" wrote in message
...
Hi
you may use the workbook event workbook_open()
for this. Place your code to check the name and unhide the sheets in
this procedure. so something like (untested)
sub workbook_open()
dim wks
application.screenupdating=false
for each wks in me.worksheets
wks.visible=true
next
me.worksheets("intro_sheet").visible=false
set wks=me.worksheets("request")
if lcase (me.name)="original.xls" then
wks.unprotect password:="your_password"
wks.range("C4:E7").locked=false
wks.protect password:="your_password"
end if
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


Anon y mous wrote:
I have a series of worksheets.

1: I have all worksheets hidden, except for a worksheet that says
macros must be anabled to properly use the spreadsheet. (It is set
up as a

The intent is:

A: if macros are disabled- this is the only worksheet visible.
B: if macros are enabled- this worksheet is hidden and others are
made visible.

C: if the current filename is "original.xls", the first worksheet
"request" is unprotected and cell range C4-E7 is unlocked, then the
spreadsheet is re-protected.

D: if the filename is not "original.xls", cell C4-E7 remains locked
in the worksheet called "request".

This spreadsheet is set up as a form for users to fill out
information and keep track of data in subsequent attached worksheets.
The intent is to keep users from recycling old forms and simply
making modifications- because they make many mistakes if they do not
start from a blank set of forms. This activity, while the user
thinks it saves lots of money- it ends up costing the company
thousands of dollars by these users trying to save a few minutes of
time. This above process will keep them from changing customer name,
case and project number- which essentially keeps them from recycling
the forms. The backdoor of hiding the pages and "must enable macros"
to use spreadsheet is to keep them from overriding the formulas...
(none of the users are smart enough to circumvent this approach.

Thanks


How do I go about coding this? I haven't found the resources yet to
figure this out on my own.

Thanks

unprotect, lock and reprotect file does not seem to work
 

It seems to me that in your macro you have told the cells to be u
locked in your statement Lock=false but you do not turn it on agai
afterwards, i'm assuming that the workbook you save does not have th
"lock cells" check box checked and this is why they remain unlocked bu
your original worksheet is locked.

I'm new to all this myself but i would try turning the lock=true o
before saving the new file.

HTH

Simo

--
Message posted from http://www.ExcelForum.com

unprotect, lock and reprotect file does not seem to work
 

I guess the original objective was not worded properly.....

The cells are not locked, but protected whent the file is originally named
"request03.xls"

When someone goes to save it and the filename is not 'request03.xls, the
group of cells b4:b9 are unprotected, locked and reprotected before the
save, so if someone reopens the file- those cells are now locked and
protected.
Hope that clarifies the difficulty I have been having..
<G


Thanks
Paul



"Simon Lloyd " wrote in message
...
It seems to me that in your macro you have told the cells to be un
locked in your statement Lock=false but you do not turn it on again
afterwards, i'm assuming that the workbook you save does not have the
"lock cells" check box checked and this is why they remain unlocked but
your original worksheet is locked.

I'm new to all this myself but i would try turning the lock=true on
before saving the new file.

HTH

Simon


---
Message posted from http://www.ExcelForum.com/

unprotect, lock and reprotect file does not seem to work
 

I guess the original objective was not worded properly.....

The cells are not locked, but protected whent the file is originally named
"request03.xls"

When someone goes to save it and the filename is not 'request03.xls, the
group of cells b4:b9 are unprotected, locked and reprotected before the
save, so if someone reopens the file- those cells are now locked and
protected.
Hope that clarifies the difficulty I have been having..
<G


Thanks
Paul



"Simon Lloyd " wrote in
message
...
It seems to me that in your macro you have told the cells to be un
locked in your statement Lock=false but you do not turn it on again
afterwards, i'm assuming that the workbook you save does not have the
"lock cells" check box checked and this is why they remain unlocked but
your original worksheet is locked.

I'm new to all this myself but i would try turning the lock=true on
before saving the new file.

HTH

Simon


---
Message posted from http://www.ExcelForum.com/



"PKyle" wrote in message
...
I tried this code, and it does not seem to unprotect, lock and reprotect
the
worksheet as indicated.. The cells remain unlocked after saving the file
with a different name, so input is still allowed into the cell range
mentioned- B4 to J9
Any ideas one what I might be doing wrong?

Thanks


"Anon y mous" wrote in message
news:6_NBc.72989$Hg2.53456@attbi_s04...


The code:

Private Sub Workbook_Open()
Dim wks
Application.ScreenUpdating = False
For Each wks In Me.Worksheets
wks.Visible = True
Next
Me.Worksheets("intro").Visible = False
Set wks = Me.Worksheets("request") ' name of
worksheet
= 'request'
If LCase(Me.Name) = "Request03.xls" Then ' name of
file = "Request03.xls"
wks.Unprotect password:="mypassword"
wks.Range("B4:J9").Locked = False
wks.Protect password:="mypassword"
End If

End Sub


The first bit works like a charm. If I disable the macro, the four
worksheets: "request", "data", "diagram" and "process" remain hidden
and
"Intro" remains visible (which simply says spreadsheet works when you
enable the macro)

However, the spreadsheet does not seem to unlock fields. I start with
the
fields locked and protected as statup default, with first use,
(conditional
with actual cell filename).. cells B4:J9 remain locked when the file has
the
filename "Request03.xls"........(in these cells, B4, C4 and D4 have been
merged, if that makes any difference, likewise, B5, C5, D5 have been
merged, as well as rows 6, 7,8, and 9)


Any ideas??

Thanks much.
Paul





"Frank Kabel" wrote in message
...
Hi
you may use the workbook event workbook_open()
for this. Place your code to check the name and unhide the sheets in
this procedure. so something like (untested)
sub workbook_open()
dim wks
application.screenupdating=false
for each wks in me.worksheets
wks.visible=true
next
me.worksheets("intro_sheet").visible=false
set wks=me.worksheets("request")
if lcase (me.name)="original.xls" then
wks.unprotect password:="your_password"
wks.range("C4:E7").locked=false
wks.protect password:="your_password"
end if
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


Anon y mous wrote:
I have a series of worksheets.

1: I have all worksheets hidden, except for a worksheet that says
macros must be anabled to properly use the spreadsheet. (It is set
up as a

The intent is:

A: if macros are disabled- this is the only worksheet visible.
B: if macros are enabled- this worksheet is hidden and others are
made visible.

C: if the current filename is "original.xls", the first worksheet
"request" is unprotected and cell range C4-E7 is unlocked, then the
spreadsheet is re-protected.

D: if the filename is not "original.xls", cell C4-E7 remains locked
in the worksheet called "request".

This spreadsheet is set up as a form for users to fill out
information and keep track of data in subsequent attached
worksheets.
The intent is to keep users from recycling old forms and simply
making modifications- because they make many mistakes if they do not
start from a blank set of forms. This activity, while the user
thinks it saves lots of money- it ends up costing the company
thousands of dollars by these users trying to save a few minutes of
time. This above process will keep them from changing customer
name,
case and project number- which essentially keeps them from recycling
the forms. The backdoor of hiding the pages and "must enable
macros"
to use spreadsheet is to keep them from overriding the formulas...
(none of the users are smart enough to circumvent this approach.

Thanks


How do I go about coding this? I haven't found the resources yet to
figure this out on my own.

Thanks