Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: save changes

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    save changes

    Hi All,

    When closing a workbook, I sometimes get queried as to whether I want to save the changes. What changes? I didn't make any. Nevertheless, I have to click No (or I could have clicked yes) to exit the file.

    Why does this happen?

    To test if this was something with my system, I created a very simple workbook with just one number in A1 on Sheet1. Exit that file and I'm out. But some of my more complicated files still bring up the dialog to save changes. I checked my VB environment and there is an xla file attached (which I had downloaded). I would think that's not the problem given the simple workbook.

    I know it's not much to click No but some workbooks are created for friends to use so this is annoying.

    TIA

    Fred

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Fred,

    If you have formulas that use dates or functions like NOW() they will be recalculated every time you open the workbook thus causing changes. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi RG,

    Thks but no dates or NOW() kind of functions.

    But it begs the question of how would one find if there are uses of such functions - w/o clicking on every cell. This is not a huge or even big spreadsheet, and most of the formulas are fills down 60-70 rows, but it would still be a pain to start clicking on each cell to double check.

    Fred

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Fred,

    If you had to go looking for such functions over an entire worksheet/workbook, you could narrow your search by highlighting only cells which contain formulas

    Code:
    Sub HighlightFormulas()
       For Each Cell In Range("A1:N77")  'CHANGE THE RANGE 
       If Cell.HasFormula = True Then
          Cell.Interior.Color = vbYellow  
       End If
    Next
     End Sub 
    
    
     Sub RemoveHighlight()
    For Each Cell In Range("A1:N77")  'CHANGE THE RANGE 
        If Cell.HasFormula = True Then
            With Cell.Interior
                .Pattern = xlNone
              End With
        End If
    Next
    End Sub
    There are many things that will make changes to a workbook and prompt for a save:

    -Code residing the Workbook or Worksheet modules that runs on an event
    -Web links or connections from other files that get updated
    -Pics or charts with external links for data
    -User defined functions incorporating the Application.Volatile command
    -Make sure Iterative Calculations is not enabled. Go to Options> Formulas> Calculations Options section and uncheck the "Enable Iterative Calculation" check box option.
    -Formulas that are linked to other workbooks

    HTH,
    Maud
    Last edited by Maudibe; 2013-06-29 at 08:44.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks Maud.

    I don't think any of the items you mentioned pertain to this workbook.

    I checked the formulas. They are IF, MAX, COUNT or COUNTIF, SUM. No Date-related functions (I did this by using CTRL+~ for each sheet; there are only 8 sheets in the workbook, of which 2 are blank for now except for a constant in A1).

    Code running on an event: There is an xla that loads with the environement for Selective Auditing that came from the Lounge. It does have some events in the This Workbook module. But if I create an essentially blank workbook (a constant in cell A1 on Sheet1), save it, reopen, it does not prompt me on close. I checked and the xla is loaded with that as well.

    But all is not lost even if can't explain the dichotomy between the "full" workbook and "blank" workbook vis-a-vis prompting or not on close. The user of this will not have this xla in her environment. But this may not be the reason.

    Web links: none in this workbook.

    Pics or charts: none in this workbook

    User-defined functions with Volatile: my workbook has no VBA. The xla does not have any Volatile commands that I saw.

    Iterative Calcs: already turned off

    Formulas linked to other workbooks: no

    Other thoughts?

    Fred

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Fred,

    If you temporarily paste the code above and run it, it will highlight the cells yellow If they have a formula. You can then verify the point RG was making without missing any cells. Many times =IF(A1="","",B1) type formulas get missed because their calculation yields a blank cell. You can remove the highlight easily by running the second code or just not saving the workbook.

    A second thought I would try is to paste the following code into the module sheet of each worksheet. If any changes are made, it will immediately pop up indicating where. This would be useful as the workbook opens or just prior to closing.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "A Change was just made in " & ActiveSheet.Name
    End Sub
    HTH,
    Maud
    Last edited by Maudibe; 2013-06-30 at 12:51.

  7. #7
    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
    Instead of putting code like that into each worksheet, you can put the single code into a ThisWorkbook object:
    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      MsgBox "A Change was just made in " & Target.Address & " of " & Sh.Name
    End Sub
    It looks for a change on EACH sheet.

    Steve

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

    Maudibe (2013-07-01)

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Yes, thanks Steve. That would make much more sense

  10. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Steve, Maud,

    I put Steve's sub in the This Workbook module and compiled. Closed workbook and no message. Opened and closed workbook again w/o any changes (other than enabling macros) - no message from Steve's 1-liner. Opened, made a change: got Steve's message. Clicked undo and got Steve's message. So I know it's working.

    Maud: I put your code for highlighting cells with formulas into my workbook (not the one for removing highlighting since I was working with a copy). The code worked fine but I didn't see any formulas that were of the "silently changing" type. I do have a lot of the =IF(a1="x","",something) type formulas. But these are all a result of filling rows down for the extent of the rows I'm working with. I have the "something" in the first few rows since they serve as a test case; the rest result in "blank" since I didn't test every row. But since they are just a result of filling from the first few rows, the formulas are the same.

    An odd thing: I have one worksheet just for me where I did a Paste List of the names used in the workbook. The 2nd col pasted by Excel is the range for the Name. This is of the form ='sheet'!cells. When I ran the macro for highlighting cells with formulas, these did NOT get highlighted. Anything special about these cells?

    Also an odd thing but I don't really care about this: if I select No to "Save Changes" when exiting the workbook, Windows does not move the icon for the Workbook on the desktop; if I select Yes, it moves it to another location (where a "new" file would go as the next spot for a new file on the Desktop).

    Fred

  11. #10
    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
    After a paste list the "formulas" listed are actually pasted as text. If you enter edit mode, then confirm, it will become a formula. [It is in an equivalent to making the format of the cell "TEXT", entering a formula and then changing the cell to a new format]

    I can not replicate the issue with the save changes and the icon on the desktop. If I save a workbook to the desktop and put it in a particular spot, whether I open and save it or open and don't save it, the icon remains in place.

    Steve

  12. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Fred,

    I entered formulas into cells using the form you described, =sheet!cells then copy/pasted them to another worksheet. I ran the highlight code on both sheets and the cells were recognized as having a formula. For some reason yours are not.

    I put Steve's sub in the This Workbook module and compiled. Closed workbook and no message. Opened and closed workbook again w/o any changes (other than enabling macros) - no message from Steve's 1-liner.
    Not sure if you are indicating that the problem has resolved OR the code has not fired but are you still being prompted to save.

    Question: Did you by chance create/modify the workbook in a different version of Excel or save the workbook in a different version as the one you are using to open it? Could it be possible that there are hidden conversions taking place when it is opened.

    Using a step-by-step systematic approach, there are 2 other ways that I would troubleshoot since you are working with a copy.

    1. Delete any code in the ThisWorkbook module, save then open and close. If the problem is still there, try deleting the standard modules one at a time, repeating the open-close testing after each change. Proceed with deleting one worksheet at a time, starting with the worksheet "just for you" because some of its formulas are referenced to another sheet, repeating the testing process after each change. If you find that after deleting a sheet that the prompt to save disappears, reload a copy of the workbook and start systematically deleting formulas from the problematic worksheet. If you get down to the last sheet, make a new sheet then delete the old one. Open and close with the blank sheet. If the problem still persists, the problem must exist with the workbook itself. I would try the second approach

    2. Working backwards, start with a new blank workbook and import your sheets one at a time from the original, again repeating the open-close testing. If you are able to import all your sheets and the problem does not display, you have produced your new working copy in the process.

  13. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, you don't need code to select formula cells - you can just use F5, click 'Special...' then select Formulas. (you can also use SpecialCells in code rather than looping)
    Regards,
    Rory

    Microsoft MVP - Excel

  14. The Following User Says Thank You to rory For This Useful Post:

    Maudibe (2013-07-01)

  15. #13
    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
    Note: Worksheet change events are only triggered when the cell CONTENTS are changed, NOT the cell's VALUE. If you have a formula like:
    =Now()
    in a cell. The contents do not change, but the value will. The change in VALUE will trigger that the workbook has changed, but will not trigger that the contents have changed.

    Steve

  16. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Thanks Rory. I knew about that one but even though F5> Special...> Formulas will select all the cells with formulas, the selection is removed with the first click. You would still need to perform some manual process to indicate which cells have the formula.

    Love your suggestion with SpecialCells and no looping

    Code:
    Sub HighlightFormulas()
        ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Select
        Selection.Interior.Color = vbYellow
    End Sub
    Using the CTRL+ ~ distorts the worksheet often requiring horizontal scrolling as well as a loss of focus on that the formula pertains to. Its just a preference thing
    Last edited by Maudibe; 2013-07-01 at 11:17.

  17. #15
    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
    There is no need to select them. The single line:
    ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) .Interior.Color = vbYellow

    Does the same thing quicker.

    But I would suggest including a line to ignore the runtime error that occurs if there are no formulas and perhaps give a message indicating the absence of formulas on the sheet. Something more like:

    Code:
    Option Explicit
    Sub HighlightFormulas()
      Dim rng As Range
      On Error Resume Next
      Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
      On Error GoTo 0
      If rng Is Nothing Then
        MsgBox "There are no formulas on the Sheet"
      Else
        rng.Interior.Color = vbYellow
      End If
    End Sub
    Steve

Page 1 of 3 123 LastLast

Posting Permissions

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