PDA

View Full Version : Solved: Unprotect Worksheet



Anne Troy
06-23-2004, 05:20 PM
I'm sure this code could use some other help, but for now, I can't figure out the syntax required to unprotect the active worksheet. There is NO password.

I'm getting "object required"

Sub SortAndSub()
ActiveWorksheet.Unprotect

Application.Goto Reference:="R1C1"
Application.CutCopyMode = False
Range("A1:C228").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range _
("C2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

ActiveWorksheet.Protect

End Sub

Jacob Hilderbrand
06-23-2004, 06:46 PM
Replace

ActiveWorksheet

With

ActiveSheet

Anne Troy
06-23-2004, 06:56 PM
Excellent, Jake.

Now, I realized I screwed up.

I don't want the range to be: Range("A1:C228")

I want it to be "used range"

Jacob Hilderbrand
06-23-2004, 08:20 PM
Dim Rng1 As Range

Set Rng1 = ActiveSheet.UsedRange 'or whatever range you want
...
...
Rng1.Sort Key1...


Another thing you can do so you don't have to continuously unprotect and reprotect the sheets is:


Option Explicit
Private Sub Workbook_Open()
For x = 1 To Sheets.Count
Sheet1.Protect UserInterfaceOnly:=True
Next x
End Sub

Anne Troy
06-23-2004, 08:30 PM
Thanks, Jake! I'll be back tomorrow. :)

Jacob Hilderbrand
06-23-2004, 09:59 PM
On a side note:

I can't believe you don't use Option Explicit in all your code.:eek: :nono

:)

In VBE Tools | Options

Check the box for force variable declaration.

This is a perfect example of why Option Explicit is a must use command. With Option Explicit instead of the generic Object Required error you get a much better error.

It will in fact highlight ActiveWorkSheet and state Variable Not Defined. So you know that you have made up your own variable and not refered to a named object as you wanted to.

Anne Troy
06-23-2004, 10:38 PM
I had it in there, and got errors. Since I never *practiced* it before, I took the option explicit out. Then I got a different error. LOL!

Your point well taken, Jake. Thanks!! We need some option explicit cops around here, huh? We should change your avatar. :)

And I'm still thinking about wiping your Tetris score.

Jacob Hilderbrand
06-23-2004, 10:53 PM
If you want to get rid of it, you have to beat me fair and square. :whip :)

I'm trying to get it to 999999. I wonder if it will roll over? or if there is another digit.

Ian Mac
06-23-2004, 11:31 PM
Hi Anne, long time!!!!

on a similar note to Jake I can't believe you don't type all of your code in lowercase,

activeworksheet would remain in lowers but activesheet would change to ActiveSheet letting you know it was an Object or a Variable you have defined.

On a more serious note, where's this Tetris? I'm going to get nothing done :)

Anne Troy
06-23-2004, 11:33 PM
The Arcade link is on the green bar that goes across EVERY page at the top.

And I keep telling y'all...I don't code! Hee hee...

You'll teach me, I suppose...

Anne Troy
06-25-2004, 06:04 AM
Okay. Jake, I'm not sure how to implement what you've said into this code. Here's the whole code:

Sub SortAndSub()
ActiveSheet.Unprotect Password:=""

Application.Goto Reference:="R1C1"
Application.CutCopyMode = False
Range("A1:C228").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range _
("C2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

ActiveSheet.Protect Password:=""
End Sub
Sub UnSubT()
Range("A1").Select
ActiveSheet.Unprotect
Selection.RemoveSubtotal
ActiveSheet.Protect
End Sub

Can you hook me up so this code works on any range? I've also attached the actual sample file I'm using.

CBrine
06-25-2004, 08:17 AM
Anne,
This should set your sort for any number of rows, as long as the column structure remains the same. The rest of the code should be OK already.


Sub SortAndSub()
ActiveSheet.Unprotect Password:=""
Application.Goto Reference:="R1C1"
Application.CutCopyMode = False
Range("A1",activesheet.Cells.SpecialCells(xlCellTypeLastCell)).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range _
("C2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

ActiveSheet.Protect Password:=""
End Sub
Sub UnSubT()
Range("A1").Select
ActiveSheet.Unprotect
Selection.RemoveSubtotal
ActiveSheet.Protect
End Sub

Anne Troy
06-25-2004, 08:26 AM
CBrine...

Who ARE you, and where did you come from?
You're TERRIFIC. I didn't check to see if it works, but you've jumped right in and got involved here, and that's TERRIFIC!!

I'll check now. :)

CBrine
06-25-2004, 10:46 AM
I'm just one of the MrExcel.com stragglers, that followed your signiture to this site. I thought a coding type forum for MSOffice would be a great change of pace, course things here don't change quite as fast.:) Yet!!

Anne Troy
06-25-2004, 10:51 AM
I'll say. Yesterday, I posted to the calendar, the average number of new members per day: 6.175. I'm anxious to see how that increases over, say, a month.

Jacob Hilderbrand
06-25-2004, 04:13 PM
xlcelltypelastcell is unreliable at best since Excel forgets what its used range is all the time.

Try this


Sub SortAndSub()
Dim Rng1 As Range
Dim LastRow As Long

'Set Range Manualy
Set Rng1 = Range("A1:C228")
'Set Range Automatically
' LastRow = Range("A65536").End(xlUp).Row
' Set Rng1 = Range("A1:C" & LastRow)

ActiveSheet.Unprotect
Rng1.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range _
("C2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("A1").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveSheet.Protect
End Sub
Sub UnSubT()
Range("A1").Select
ActiveSheet.Unprotect
Selection.RemoveSubtotal
ActiveSheet.Protect
End Sub

Anne Troy
06-25-2004, 05:11 PM
Jake: You've got "set range manually".

I can't do that; it's going to be different every day.
Or am I misunderstanding?

And could you approve my kb entries? Puh...leeeze?? :)

Jacob Hilderbrand
06-25-2004, 06:09 PM
I put both set manually and automatically. Just uncomment the automatic parts and comment/delete the manual part.

Ill check out the kb entries.

Anne Troy
06-25-2004, 06:18 PM
Thanks!!