Conditional unprotect and unlocking of cell range

A

Anon y mous

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
 
F

Frank Kabel

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
 
I

ijb

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
 
A

Anon y mous

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
 
P

PKyle

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
 
S

Simon Lloyd

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
 
A

Anon y mous

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
 
A

Anon y mous

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
 
Top