Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi gang,
    Using Excel 2002.
    Is there a way I could hide comments in an Excel sheet.
    Even if I lock the sheet to prevent any changes, if the person clicks on the "show comments" button on the track changes toolbar he/she can see the comments I made. Those comments are notes for my eyes only.
    Any way I could hide them for transmittal.
    tks.
    Johanne Champagne
    Montreal (Quebec) CANADA

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JC,

    Here's a rather blunt instrument approach, but it does work.

    Code:
    Sub HideShowComments()
    
      Dim oCtl As Object
      
      Set oCtl = Application.CommandBars("Worksheet Menu Bar").Controls("Tools"). _
                 Controls("Options...")
    
       If Environ("username") = "Owner" Then
        Application.DisplayCommentIndicator = xlCommentIndicatorOnly
        oCtl.Enabled = True
       Else
        Application.DisplayCommentIndicator = xlNoIndicator
        oCtl.Enabled = False
       End If
    Either call this from your Auto_Open() macro or just rename it to Auto_Open. Then change the username value from "Owner" to what ever your sign on is. You can then work on the file with full access to the comments while anyone else on another machine will not be able to. Of course they will also not be able to change any other Options!!!!! Maybe someone else out there knows how to get down into the View page of the Options popup and just disable that page or even the individual controls.

    Please Note: you will also have to reset the menu on exit since this change in the menu structure is persistent.

    I hope you find this useful or at least it points you in a useful direction.

    RG

    P.S. Here's a routine you can use during testing to toggle the settings.

    Code:
    Sub Test()
    
      Dim oCtl As Object
      
      Set oCtl = Application.CommandBars("Worksheet Menu Bar").Controls("Tools"). _
                 Controls("Options...")
    
       oCtl.Enabled = Not oCtl.Enabled
    
       Application.DisplayCommentIndicator = Not Application.DisplayCommentIndicator
    
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    England
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As you suggest that locking the spreadsheet is an option, why not just save it and send it as a .csv file?

Posting Permissions

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