Alerts When Sharing and Unsharing

Sharing workbooks in Excel seems to be a disaster, at least to me. Yet the fact that so many people have problems with it must mean that they use it. Attempting to change a shared workbook in code will often cause the code to fail. So, like with protection, you have to unshare, do your thing, then re-share. This causes Excel to prompt the heck out of you about workbooks that already exist and losing change history. Fortunately, the DisplayAlerts property of the Application object handles these alerts.

‘unshare
Application.DisplayAlerts = False
    ThisWorkbook.ExclusiveAccess
Application.DisplayAlerts = True
 
‘Do stuff here

‘re-share
Application.DisplayAlerts = False
    ThisWorkbook.SaveAs , , , , , , xlShared
Application.DisplayAlerts = True
Posted in Uncategorized

9 thoughts on “Alerts When Sharing and Unsharing

  1. As always, a useful comment. I’m actually scared to even open workbooks read-only on the network because of the confusing, contradictory messages that come up when you go to close the file.

    The real reason I’m posting, though, is I like the code textbox with scrollbar that you used in this post. That’s new isn’t it? Very cool.

  2. >>The real reason Iím posting, though, is I like the code textbox with scrollbar that you used in this post. Thatís new isnít it? Very cool.

    I’ll be the crank who disagrees. The whole bit of code was only like 11 lines, including blank lines. Putting it into this smaller textbox means I have to scroll up and down to figure it out. And it takes two whole clicks to go all the way down, and two more to come all the way back up.

    I’m glad that’s the worst thing I have to rant about today!

  3. I agree with both of you. I think it’s cool (because I don’t know how it happened or how it works) and I hate it (for the scrolling reason). I’ll post about this “experiment” later, but I found this new method of posting code. There was no textbox when I previewed the post (and there still isn’t when I edit it). I’ve pinged the author or php plug-in to see what he has to say about it.

  4. It’s good, cus the codes like code (you what!?), and the apostrophises are right. Lately when I have been pasting code, I have to good and change the apostrophises in the VBE. Agree about the scrolling thing though.

    ‘My code stuff

    dim Ross a Long
    dim Ross a Short

    msgbox “did this test work?”

  5. Ross – Well, did it? I can use the <code> tags when I post, but I like the pretty colors and they don’t respect my indenting. I’d have to add my own font coloring and arseloads of nbsp’s to get it to work right. I hope the guy that wrote this plug in gets back to me, otherwise I’ll have to go back to modifying Juan Pablo’s VBA. I thought that would be pretty simple, but there’s alot more to converting code to html than I thought.

  6. Why not try Bloglines.Bloglines is much better than newsgator. You can synchronize bloglines with blogbot for outlook. Folder hierarchy is synchronized between Outlook and Online via blogbot. Itís much better than newsgator between outlook and online.
    If you like desktop news client, You can use Greatnews to synchronize bloglines like newsgator using feeddemon. Good news is Greatnews is a wonderful free software but feeddemon isnít.Folder hierarchy is synchronized between Bloglines and Greatnews. Feeddemon doesnít synchronize Folder hierarchy between newsgator online and itself. Greatnews can be found at :http://www.curiostudio.com/
    If you like newsgator for outlook, donít worry, blogbot does the same and better job for outlook. blogbot can be found at http://www.blogbot.com/out/.
    Even you uses several computer, you wonít read the same news twice. cause itís synchronized online, at outlook via blogbot, at desktop by greatnews.
    Bloglines doesnít offer a desktop service? Thatís not true, check out Greatnews!

  7. Hiding Excel warnings during automation

    Well, I sorted out my problem of confusing warnings appearing whilst controlling Excel with VBA. Turns out there is an Application.DisplayAlerts property which, when set to false, hides warnings such as the one I was getting. It took a little Googling…

  8. Sharing and merging of Excel can be a tricky business. Perhaps an easier way to utilize the sharing and merging features of Excel is to include the Excel add-in Distributed Spreadsheet. I have found this tool very useful for those occasions when gathering info from a group of colleagues is needed. The built-in versioning is also an added benefit.

    Worth a look.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.