Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    Illinois, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unwanted macro file save when closing Excel (Excel 97 / WinNT 4)

    I provide an Excel file (RVAmacro99.xls) which contaings several macros for use on my company's network. An auto-open macro creates a toolbar to access the other macros in RVAmacro99.xls. People use the other macros in the file to load text files and chart data.

    Even though I have users open RVAmacro99.xls in Read-Only mode (password protected) and it is hidden, I get the following problem:

    When closing out Excel, users are prompted with the "helpful" Excel prompt:
    "Do you want to save the changes you made to RVAmacro99.xls?".

    Now, if someone presses Yes and goes ahead and saves the file somewhere, my toolbar icon (which sticks on their toolbar for their next session) now refers not to the original RVAmacro99.xls file but to this copy---and the copy's macros don't work properly. The code that creates the toolbar in the first place doesn't have the network location hard-coded because some users need to be able to use this on non-connected laptops.

    How can I prevent this unwanted save from occuring? Its pretty hard to prevent novice users from making this mistake on occasion.

    Thanks

  2. #2
    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

    Re: Unwanted macro file save when closing Excel (Excel 97 / WinNT 4)

    You could add this to the workbook close event:

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If ThisWorkbook.ReadOnly = True Then _
    ThisWorkbook.Saved = True
    End Sub</pre>


    If it is read-only, excel is told that it is already saved.

    Steve

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

    Re: Unwanted macro file save when closing Excel (Excel 97 / WinNT 4)

    Steve's suggestion should work fine. As an alternative, you could save your workbook as an add-in (.xla) and let users install it via Tools | Add-Ins...

  4. #4
    New Lounger
    Join Date
    Nov 2002
    Location
    Illinois, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unwanted macro file save when closing Excel (Excel 97 / WinNT 4)

    Thanks Steve, this works just perfectly1

    Hans, I hear you about making this application an add-in. They're not trouble-free either, unfortunately---there's a couple computers around there that seem to keep dropping the listing for the shipped-with-Excel "Data Analysis" tool-pak add-in. This happens even though the listing (Tools > Addins still includes the Toolpaks).

    Reading one of John Walkenbach's VBA books, I sense (but don't read) that the XLAs that are "checked" in the above dialog box are loaded from disk each time Excel is loaded. Is that right? If so I COULD make the file loaded from either the local laptop drive OR a network drive. I worry about users getting the latest version of file (with fixes like the one Steve suggested). How can the user switch the location from one place to another---just reload it? I guess I worry about "managing" the user's Add-in list. With my macro now, I don't have to worry about it.

    Thanks again, you guys are the best., from a long-time lurker

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

    Re: Unwanted macro file save when closing Excel (Excel 97 / WinNT 4)

    In your situation, I would stay with Steve's solution. Your users won't have the problems they are experiencing with add-ins.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Unwanted macro file save when closing Excel (Excel 97 / WinNT 4)

    I'm late to the party, and may not follow the problem, but I'm curious why the custom TB or code refers to other WB's? If you call some of the 5,000 or so other face ID's for the custom TB , the Workbook code which creates the custom TB every time the file is opened shouldn't need to refer to any other location or file. If the issue is of linked file location, you can test the Workbook's own file location and if the file is saved in other that its original/default location, stop the code from running.

    But Steve's solution is way simpler! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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