Macro problem with protecting worksheet...........

P

PKyle

I have a workbook with 5 active worksheets. The firt worksheet is named
"request".
If "request" is the active worksheet, it locks the fields below.... if I am
on another worksheet, it does not lock the fields in "request" as noted
below.

What did I miss?
Thanks



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Sheets("Request").Range("K4") = Now
Set wks = Me.Worksheets("request")

ActiveWorkbook.Unprotect password:="mypassword"

ActiveSheet.Unprotect password:="mypassword"
Range("B4:B9").Select
Selection.Locked = True
Range("J2:M2").Select
Selection.Locked = True
Range("A26:L33").Select
Selection.Locked = True
Range("A14:L22").Select
Selection.Locked = True
Range("A39:L54").Select
Selection.Locked = True
Range("A55:L60").Select
Selection.Locked = True
Range("K4:K9").Select
Selection.Locked = True

ActiveSheet.Protect password:="mypassword"
ActiveWorkbook.Protect password:="mypassword"



End Sub
 
T

Tom Ogilvy

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wks as Worksheet

Me.Unprotect password:="mypassword"

With wks
.Unprotect password:="mypassword"
.Range("K4") = Now
.Range("B4:B9,J2:M2,A26:L33,A14:L22,A39:L45," & _
"A55:L60,K4:K9").Locked = True
.Protect password:="mypassword"
End With
Me.Protect password:="mypassword"
End Sub
 
J

Jim Rech

If you don't tell VB which sheet to work on it uses the active sheet

doesn't name a sheet while this does:

wks.Range("B4:B9").Select

Also, don't you want to unprotect wks rather than the active sheet?:

wks.Unprotect password:="mypassword"


Btw, all you selecting/locking code can be boiled down to one line since
selecting isn't necessary:

wks.Range("B4:B9,J2:M2,A26:L33,A14:L22,A39:L54,A55:L60,K4:K9").Locked =
True

--
Jim Rech
Excel MVP
|I have a workbook with 5 active worksheets. The firt worksheet is named
| "request".
| If "request" is the active worksheet, it locks the fields below.... if I
am
| on another worksheet, it does not lock the fields in "request" as noted
| below.
|
| What did I miss?
| Thanks
|
|
|
| Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
| Boolean)
|
| Sheets("Request").Range("K4") = Now
| Set wks = Me.Worksheets("request")
|
| ActiveWorkbook.Unprotect password:="mypassword"
|
| ActiveSheet.Unprotect password:="mypassword"
| Range("B4:B9").Select
| Selection.Locked = True
| Range("J2:M2").Select
| Selection.Locked = True
| Range("A26:L33").Select
| Selection.Locked = True
| Range("A14:L22").Select
| Selection.Locked = True
| Range("A39:L54").Select
| Selection.Locked = True
| Range("A55:L60").Select
| Selection.Locked = True
| Range("K4:K9").Select
| Selection.Locked = True
|
| ActiveSheet.Protect password:="mypassword"
| ActiveWorkbook.Protect password:="mypassword"
|
|
|
| End Sub
|
|
 
P

PKyle

Thanks, I gave both answers a shot- I'm not sure what I am missing- I
get errors with this code now.
Error runtime 91: obj variable or with block not set......

I have 5 worksheets in this workbook.
"Request"
"instructions"
"Analyses"
"Data"
"Summary"
are the 5 workbook names

I only need to lock the field ranges stated for the worksheet called
"Request", since the others are already protected.
My earlier macro works if I am in the worksheet called "request". How can I
jump to this worksheet and display it in the before save event- so the code
that I have that works is executed ??
ie. the before save event switches view to the "request" worksheet as
the active sheet and then performs the working code??


Thanks
Paul
 
T

Tom Ogilvy

Looks like the line that sets wks got left behind. this worked for me:

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wks As Worksheet
Set wks = Me.Worksheets("Result")
Me.Unprotect Password:="mypassword"

With wks
.Unprotect Password:="mypassword"
.Range("K4") = Now
.Range("B4:B9,J2:M2,A26:L33,A14:L22,A39:L45," & _
"A55:L60,K4:K9").Locked = True
.Protect Password:="mypassword"
End With
Me.Protect Password:="mypassword"
End Sub
 
P

PKyle

That's what I thought too- I had already tried that. The next error I get
is `1004- unable to set locked property with the range locked statement....
I'm going to split them out one by one again to see where it might be
hanging up.
??
Thanks
Paul.
 
P

PKyle

Its hanging with two cell ranges: B4:B9 and K4:K9 (I apostrophied
B4:B9 and then it provides an error for the last one- K4:K9)
I'm puzzled as to why it is finding errors with these cells. THey are
unprotected....

Paul
 
T

Tom Ogilvy

Perhaps they make up a part of a merged cell area.

As I said, on a pristine worksheet, the code worked fine for me.
 
P

PKyle

one group is a merged cell group, the other? I'll have to check... will
try when I get home this evening
Didnt' think that could be it, and almost put those words in the text- one
was a merged group of cells!

Thanks!

Paul
 
P

PKyle

That was it!
took a while to get to it....(sunday night) and kept forgetting that I
needed to save only while macros were disabled.

It works like a charm. Now I can implement a form that will work!

Thanks
Paul
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top