Unprotect Worksheet

[Anne Troy]

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]

Replace


ActiveWorksheet

With


ActiveSheet

[Anne Troy]

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]

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]

Thanks, Jake! I'll be back tomorrow. :)

[Jacob Hilderbrand]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

Thanks!!