Unprotect Worksheet

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"

Code:

---

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

---

Replace

Code:

---

ActiveWorksheet

---

With

Code:

---

ActiveSheet

---

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"

Code:

---

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:

Code:

---

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

---

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

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.

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.

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.

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 :)

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...

Okay. Jake, I'm not sure how to implement what you've said into this code. Here's the whole code:

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.

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.

Code:

---

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

---

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. :)

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!!

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.

xlcelltypelastcell is unreliable at best since Excel forgets what its used range is all the time.

Try this

Code:

---

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

---

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?? :)

I put both set manually and automatically. Just uncomment the automatic parts and comment/delete the manual part.

Ill check out the kb entries.

Thanks!!