Excel 97 Screen Refresh (Resolved)

[TheFIDDLER]

Anyone have a bit of code that I can use to force a screen refresh using VBA in Excel 97.

I know of the ScreenUpdating = true property, but I think I am looking more for a ScreenUpating = NOW property.

I ran accross a reference to a ScreenRefresh method in "Excel 2002 VBA" by Wrox Prex, but they just hint at it and never give an example or actual setup for it. I can't find it in the application model for 2002 or 97.

ScreenUpdating works to show commands as they happen. I want to refresh the screen -after- all the fun stuff has happened. Kind of like sending a wakeup call to the system to show the current status at that point in time.

[RobDog888]

What about the basic DoEvents method? It doesn't work for your situation?

[TheFIDDLER]

Just tried it. Nope.

I am using a VBA form in a workbook that has many calculations, and my code hides/unhides collumns as required. For which it really slows down code if this is done with screenupdating set to true.

On the same form, I have a combobox where the user can select a category of items to view. Clicking the box doesn't really do anything. I use the listvalue when the user clicks a command button to actually do something with it.

Bad part is that with screenupdating set to false, my combobox does not redraw the part of the screen which it used to display the data.

And where this is really inert - there is no active event to redraw, screenupdate doesn't work for me. I tried screenupdating as part of the combobox change event, but a) nothing happens b) if I set it to true then I have no second chance to reset it to false.

I tried a set screenupdating to true, followed by a Doevents, followed by a set screenupdating to false in the combobox change event and still got nothing.

Which brings me back to my original request for some type of screen refresh command...

[RobDog888]

Try the combo box click or keyup event instead. The change
event is when a list item is actually changed or if the style is 0 or
1 and the user types something into it.

[TheFIDDLER]

The click event can at least turn screen updating on.

Still only half the battle.

I need it to turn screen updating on, update, and then turn screen updating off. And if I turn it on, doevents, and turn it off, nothing happens. So I can only use it as an on switch.

Actually, I figured out a way around this - but it reaks of bad coding. Since my click event for the combobox can be used to set screen update to true, and I need this whenever someone changes a value in the combobox, I can add the line for screen updating = false, to the start of each subroutine that is callable from within the form. Thus technically, being off before any formulas are recalculated or any collumns are hidden.

[RobDog888]

I can't find it in the application model for 2002 or 97.

It is not in 2003 either. I even searched M$ Office On-Line - Nothing.
Must be a custom written function that the author is referring to.

[TheFIDDLER]

Yeah

I double check my book - author is detailing the application object and the following is listed:

"Screenupdating - boolean - Set/Get whether Excel updates its display while a procedure is running. This property can be used to speed up procedure code by turning off screen updates during processing. Use with the ScreenRefresh method to manually refresh the screen." - from "Excel 2002 VBA"

Except I doubt the ScreenRefresh method actually exists since I can't find any other references to it.

[RobDog888]

Maybe its a typo or something because if you can't find it in 2002
or 97 and I can't find it in 2003 then perhaps you can contact the
author and get him to elaborate on this situation?

[amer7862000]

wat bout Application.Refresh/Application.RefreshAll or somethin..
(im new)

[RobDog888]

There is no Application.Refresh or .RefreshAll
It is only in the Workbook object and it doesn't do screen refreshing.
From the help file...

RefreshAll Method
Refreshes all external data ranges and PivotTable reports in the specified workbook

Refresh
Refreshes a Chart, PivotTable, ListObject, QueryType, or
XMLDataBinding object's datasource and does not commit
changes in the object.

[amer7862000]

Originally posted by RobDog888
There is no Application.Refresh or .RefreshAll
It is only in the Workbook object and it doesn't do screen refreshing.
From the help file...

RefreshAll Method
Refreshes all external data ranges and PivotTable reports in the specified workbook

Refresh
Refreshes a Chart, PivotTable, ListObject, QueryType, or
XMLDataBinding object's datasource and does not commit
changes in the object.

ive acknowledged that!

[ZeBula8]

Use the Me.Repaint method.

from the help:
The Repaint method is useful if the contents or appearance of an object changes significantly, and you don't want to wait until the system automatically repaints the area.

[TheFIDDLER]

Me.Repaint

Me.verythankfull !

Thank you! This will come in very handy at many places within my code. This is actually exactly what I had been looking for. Excel is notorious for not redrawing once memory consumption increases.