Consulting

Results 1 to 19 of 19

Thread: Solved: Unprotect Worksheet

  1. #1
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location

    Solved: 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"

    [vba]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[/vba]
    ~Anne Troy

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Replace
    [vba]
    ActiveWorksheet
    [/vba]
    With
    [vba]
    ActiveSheet
    [/vba]

  3. #3
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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"
    ~Anne Troy

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    [vba]

    Dim Rng1 As Range

    Set Rng1 = ActiveSheet.UsedRange 'or whatever range you want
    ...
    ...
    Rng1.Sort Key1...
    [/vba]

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

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

    [/vba]

  5. #5
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Thanks, Jake! I'll be back tomorrow.
    ~Anne Troy

  6. #6
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    On a side note:

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



    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.

  7. #7
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

  8. #8
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    If you want to get rid of it, you have to beat me fair and square.

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

  9. #9
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    5
    Location
    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

  10. #10
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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

  11. #11
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Okay. Jake, I'm not sure how to implement what you've said into this code. Here's the whole code:

    [vba]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[/vba]

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

  12. #12
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    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.

    [VBA]
    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
    [/VBA]
    The most difficult errors to resolve are the one's you know you didn't make.


  13. #13
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

  14. #14
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    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!!
    The most difficult errors to resolve are the one's you know you didn't make.


  15. #15
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

  16. #16
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    xlcelltypelastcell is unreliable at best since Excel forgets what its used range is all the time.

    Try this

    [vba]
    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
    [/vba]

  17. #17
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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??
    ~Anne Troy

  18. #18
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    I put both set manually and automatically. Just uncomment the automatic parts and comment/delete the manual part.

    Ill check out the kb entries.

  19. #19
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Thanks!!
    ~Anne Troy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •