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

    Comments Question (Excel 2002/2003)

    Hi

    I have a worksheet that has headers that need to be abbreviated, I want to add a comment to the cell to explain the abbreviation, Hovever it has to have an autofilter set and the sheet is protected. I have formatted the headers to have no protection when the sheet is protected bu still cannot see the comments, is there a way round this please.

    I have attached a simple example file.

    Many Thanks

    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: Comments Question (Excel 2002/2003)

    If you tick the check box "Edit objects" in the Tools | Protection | Protect Sheet dialog, comments will be displayed.

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

    Re: Comments Question (Excel 2002/2003)

    Hi Han

    I,m sorry I done it again I forgot to mention the sheet is filtrered by code. <img src=/S/smash.gif border=0 alt=smash width=30 height=26> I have attempted to add the code required to my macro, but it does not work.
    Sub ProductListSort()
    '
    ' ProductListSort Macro
    ' Macro by Alan Bradshaw

    '
    Sheets("Summary").Unprotect
    Range("A12").AutoFilter
    ActiveSheet.Protect AllowFiltering:=True
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True
    'If you want to turn on AutoFilter after protecting, you must also specify that you can still modify the worksheet through macros:

    ActiveSheet.Protect AllowFiltering:=True, UserInterfaceOnly:=True
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True
    Range("A12").AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="<>"
    Sheets("Summary").Protect
    End Sub

    Regards

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

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

    Re: Comments Question (Excel 2002/2003)

    Just adding multiple AutoFilter and Protect statements randomly won't help. Does this do what you want?

    Sub ProductListSort()
    ActiveSheet.Unprotect
    Range("A12").AutoFilter Field:=1, Criteria1:="<>"
    ActiveSheet.Protect AllowFiltering:=True, DrawingObjects:=False, _
    Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
    End Sub

    Note that there is only one Protect statement and only one AutoFilter statement.

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

    Re: Comments Question (Excel 2002/2003)

    Hi Hans

    Thanks for the reply, your code works fine in my test sheet but not in the actual worksheet where I wish to use it, I will have to do some more checking.

    Thanks again

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

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

    Re: Comments Question (Excel 2002/2003)

    Hi Hans

    In my master sheets it works wth the clear code but not the sort code.

    It works with this code.
    Sub ProdListClear()
    '
    ' ProdListClear Macro
    ' Macro recorded 11/07/2005 by XX0824

    Sheets("Summary").Unprotect
    Selection.AutoFilter Field:=1
    ActiveSheet.Protect AllowFiltering:=True, DrawingObjects:=False, _
    Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
    End Sub

    But not with this one

    Sub ProductListSort()
    ActiveSheet.Unprotect
    Range("A12").AutoFilter Field:=1, Criteria1:="<>"
    ActiveSheet.Protect AllowFiltering:=True, DrawingObjects:=False, _
    Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
    End Sub

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

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

    Re: Comments Question (Excel 2002/2003)

    In what sense doesn't it work?
    - Do you get an error message (if so, what does it say)?
    - Does the code do something different from what you want
    - Does the code do nothing at all?

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

    Re: Comments Question (Excel 2002/2003)

    Hi Hans

    I don't get any errors it filters and protects the sheet.

    If I run the Filter Macro I cannot hover on the cell and see the Comments. However when I run macro to remove the filter the sheet is protected and I can hover on the cell and see the comment.

    Hope this is clear.

    Regards

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

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

    Re: Comments Question (Excel 2002/2003)

    Hi Hans

    Once again thank you for your patience, I have solved the problem, it was the code in a Control Button causing the problem.

    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
  •