No screen updating after ActiveWorkbook.Save

bmeijers

New Member
Joined
Nov 30, 2005
Messages
3
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

New Member
Joined
Nov 30, 2005
Messages
3
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
For what it is worth I did exactly as you said and had no problem.
The macro runs Ok with normal save too.
 

bmeijers

New Member
Joined
Nov 30, 2005
Messages
3
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,023
Messages
5,834,996
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top