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

  • Thread starter Thread starter PKyle
  • Start date Start date
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
 
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
 
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
|
|
 
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
 
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
 
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.
 
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
 
Perhaps they make up a part of a merged cell area.

As I said, on a pristine worksheet, the code worked fine for me.
 
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
 
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
 
Back
Top