Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2011
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question Tracking changes problem in Excel 2007

    I'm using Windows XP Professional and Excel 2007.

    Is there any way to change the default settings for the "Highlight Changes" box once I've saved the workbook?

    I have 2 managers here that need to edit the spreadsheet and return it to me with their changes marked. We tried the "shared workbook" but it didn't work properly for our needs. Maybe I was doing it wrong - I don't know.

    So, we decided to try the "highlight changes" instead. Only problem is, they are forgetting to go in and change the "when" to "all" and the "who" to "everyone" so it's not capturing everything.

    Is there a way to make this default to "all" and "everyone" each time so that we don't have to remember?

    I am sure there must be an easier way to edit these workbooks - if someone knows, please share with me!

    Thanks!

  2. #2
    New Lounger
    Join Date
    Jun 2007
    Location
    London, United Kingdom
    Posts
    9
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi there,

    How about a macro that starts when you open the workbook and runs the application.sendkeys method to pass the keyboard shortcut codes to do what you want, e.g. ALT +R +G +H to get to the Highlight Changes dialog and then T and O to apply your settings?

    Derek

  3. The Following User Says Thank You to derek.white For This Useful Post:

    tlopez87 (2011-06-02)

  4. #3
    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
    You could also do it with VBA to the this workbook object (change any options if desired)

    Code:
    Private Sub Workbook_Open()
      With ThisWorkbook
        .KeepChangeHistory = True
        Application.DisplayAlerts = False
        .SaveAs Filename:=.FullName, _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
            AccessMode:=xlShared
        Application.DisplayAlerts = True
        .HighlightChangesOptions _
          When:=xlAllChanges, _
          Who:="Everyone"
        .ListChangesOnNewSheet = False
        .HighlightChangesOnScreen = True
      End With
    End Sub

    Make frequent backups if you are going to do this. Shared workbooks are (not surprisingly) notorious for getting corrupted. You will have to unshare it (losing the previous history) to add the code....

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    tlopez87 (2011-06-02)

  6. #4
    New Lounger
    Join Date
    May 2011
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I'm finding that I can't record macros in a shared workbook, and I can't enter the VBA code above in a shared workbook. I don't understand macros or coding well at all. Maybe I'm doing something wrong. Will I have to create the Excel file from Access, run the macro (or insert the VBA code) PRIOR to doing anything else with my workbook, including saving it?

    Thanks,
    Traci

  7. #5
    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
    You can not edit code in a shared workbook. You need to do all the editing before sharing it (or removing sharing, edit and then reshare it). Code should still run after it is shared.

    Steve

Posting Permissions

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