Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Code will not work? (Excel 2003)

    Hi

    I have a problem with the code below the allow filter does not work,

    Private Sub Workbook_Open()
    Worksheets("A-D Compass Progress Report Q1").Select
    Worksheets("A-D Compass Progress Report Q1").Unprotect Password:="aw"
    Range("A3:AQ3").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFiltering:=True
    Worksheets("A-D Compass Progress Report Q1").Protect Password:="aw"
    End Sub

    Regards
    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code will not work? (Excel 2003)

    Why do you protect the same sheet twice at the end of the code?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Code will not work? (Excel 2003)

    Hi Hans

    I don't understand I can only see Unprotect once and protect once, what am I missing?

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code will not work? (Excel 2003)

    ActiveSheet.<font color=red>Protect</font color=red> DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFiltering:=True
    Worksheets("A-D Compass Progress Report Q1").<font color=red>Protect</font color=red> Password:="aw"

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Code will not work? (Excel 2003)

    Hi Steve

    I removed the last row and the filter par works, and the sheet is protected but without the password/?

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code will not work? (Excel 2003)

    Why don't you add the password to the remaining line then?

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFiltering:=True, Password:="aw"

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code will not work? (Excel 2003)

    By the way, what is the purpose of unprotecting and protecting the sheet? You don't need to do that if you want to select some cells.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Code will not work? (Excel 2003)

    Hi Hans

    Thanks for that I have never used the Password:= anywhere other than for a worksheet, so it did not readily easy come to me.


    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code will not work? (Excel 2003)

    But this *is* for a worksheet! <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Code will not work? (Excel 2003)

    Hi Hans

    Sorry for the confusion, what I was trying to say was I had only used it for something like Sheets("Input").Protect password: = "aw"

    Regards Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Code will not work? (Excel 2003)

    Hi

    What I was looking for, is to have the worksheet protected when opened except for the range I needed to filter.

    I hope this is clear.

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code will not work? (Excel 2003)

    You don't need code for that. Simply unlock the cells you want to be editable, and protect the worksheet interactively.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code will not work? (Excel 2003)

    ActiveSheet is a sheet too - it simply refers to whichever sheet is the active one at the moment the code is run.

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Code will not work? (Excel 2003)

    Hi Hans

    Thanks very much for the instructions.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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