No screen updating after ActiveWorkbook.Save

[bmeijers]

Hello,

I'm currently struggling with a strange problem. When I execute the ActiveWorkbook.Save method from within VBA the BeforeSave event is fired. In the Workbook_BeforeSave eventhandler I do my stuff, but strangely enough I cannot see what is happening in my Excel screen. For instance if I select another sheet or select another cell the screen is not updated. Only value assignments to cells are shown. This gives me a hard time to debug my code in the Workbook_BeforeSave eventhandler. The EnableEvents and ScreenUpdating properties are both set to true. This problem does not occur when I enter the Workbook_BeforeSave eventhandler via Excel's Save button.

I use Excel 2002, but this problem also occurs in Excel 97.

Does anybody know a solution/workaround?

Boudewijn

 

[BrianB]

Maybe ...........
When "strange things" occur I now suspect some form of data or code corruption. This is frequent enough that I now *expect it to happen* at some stage when working on a larger project. Usually this is covered by making backups at intervening stages. 'Version 1' .. etc.

Another thing, on larger projects we have to avoid "lazy programming". Often we can get away with x=Range("A1"), but I have noticed that such code can work at the beginning of a project and fail later on. This makes it a pig to debug, because we naturally suspect the newer code first. So we have to be explicit with such things as
WorkBooks("Book1.xls").Worksheets("Sheet1").Range(A1").Value
.. or use of suitable objects.

Look for code where Excel has to do a lot of work in a single line and use variables instead. Problems often occur when using string concatenation for file names and ranges. Code works early on, but Excel gives up later.
eg. rg = "A" & var1 & ":X" & var2 ... ActiveSheet.Range(rg).Copy

Another clue to your problem might be :-

I use Excel 2002, but this problem also occurs in Excel 97

I use these 2 versions and, although obeying the rule "set projects up in the earlierst version", sometimes make the error of saving from XL2000 - when the code can fail in XL97. I now put "XL97" in the file name of my master workbook and make sure that it is always kept apart from the XL2000 version - which is not used for any development.

If you do not get anyother ideas, I suggest you "clean" your project (in XL97) by transferring to a new workbook. Perhaps the code first by copy/pasting into Notepad (not Word) and then from there to new modules.

Make a backup with only the code modules, and then copy worksheets across. If you still get problems discard that, open the backup, insert new sheets and copy/paste special Formulas and Formats for each sheet - which does values too. The only thing missing (?) will be named ranges. Could write code to transfer them if necessary.

Hope this helps.

 

[bmeijers]

BrianB, Thanks for your response! I'm a resonable experienced vba developer, so I kow what I'm doing

You can reproduce my problem yourself. In an empty workbook add a module. Cut and paste this code below

public sub test()
activeworkbook.save
end sub

In the ThisWorkbook object copy and paste this code below

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet2").select
End Sub

Split the screen up in excel and vbe screen. Put a breakpoint on line Sheets("Sheet2").select and step into public sub test() with F8 and see what happens.

 

[BrianB]

For what it is worth I did exactly as you said and had no problem.
The macro runs Ok with normal save too.

 

[bmeijers]

That is strange. I just checked again: in both versions 2002 and 97 the excel window is not updated to reflect the selection of Sheet2. Did you see it?