+ Reply to Thread
Results 1 to 2 of 2

VBA change shared workbook advanced settings

  1. #1
    Registered User
    Join Date
    01-18-2013
    Location
    Funtown, USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    VBA change shared workbook advanced settings

    One team that I oversee works from shared workbooks all day.
    The assistant manager runs appx 40 reports a day, making each workbook shared, and the team clears each report.

    I'm trying to save my AM a bit of time by letting VBA handle the sharing of the workbook.
    I already have the code in place that shares the book - no issues

    Please Login or Register  to view this content.
    What I'm trying to do now is see if there is a way using VBA to manipulate the advanced options in the Share Workbook dialogue box, namely the "Keep change history for:" and "Update changes" options.

    As a default, the settings are to keep the change history for 30 days and update changes "When file is saved". I would like for the code to set the change history for 365 days and to set the "Update changes" option to "Automatically every 5 minutes".

    If I had to choose between the two, the automatically save every 5 minutes is more valuable to me.

    Thanks for your time and any help is appreciated.

  2. #2
    Registered User
    Join Date
    01-18-2013
    Location
    Funtown, USA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: VBA change shared workbook advanced settings

    Well, after much searching I was finally able to piece everything together!
    Much thanks to this page over at tek-tips for showing the methods that I needed to use. What I had to use were the following:

    KeepChangeHistory
    ChangeHistoryDuration
    AutoUpdateFrequency
    AutoUpdateSaveChanges

    The KeepChangeHistory/duration methods worked just fine, but I had to do a lot of trial/error with the autoupdate methods. I had originally tried to set the AutoUpdateSaveChanges first, but I'd always get an error. I switched the order and put the Frequency first and things worked like a charm. Maybe this can help someone else now.

    Code below.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Shared Workbook with Individual User Settings?
    By rpmuha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2013, 09:54 AM
  2. Shared Workbook - Print Settings?
    By Al Franz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-08-2011, 11:55 AM
  3. cant not change history on a shared workbook
    By cyndi in forum Excel General
    Replies: 0
    Last Post: 12-15-2005, 05:20 PM
  4. Shared workbook losing print settings
    By koppers in forum Excel General
    Replies: 0
    Last Post: 11-11-2005, 02:35 AM
  5. [SOLVED] Shared Workbook Settings, End User Update Changes Refresh Rate
    By JESSENBE in forum Excel General
    Replies: 0
    Last Post: 07-19-2005, 04:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1