Screen Updating

[ercedwards]

I have a macro that clears all of the cells in a workbook before I repopulate them with new data.
The first step in the macro clears all of the Command Button captions. I then do an Application.Screenupdating=True

I then carry on to clear all other cells. As these are being cleared they appear as cleared on the screen.

The issue I have is that the Command Button captions don't appear as clear even though I cleared them first.

Since I repopulate everything immediately after, the original CB captions still show even though I have changed them and all of the correct data is in each cell.

Very strange. I've tried inserting WAIT, DoEvents and even re-selecting the workbook but neither helped.

Suggestions such as VBA code to refresh the Command Buttons?

Thanks kindly

 

[Yongle]

With Active-X Command Buttons, this works for me

Sub ClearCellsAndCommandButtons()
    Dim objX As Object
    With ActiveSheet
        .UsedRange.Clear
        For Each objX In .OLEObjects
            If TypeName(objX.Object) = "CommandButton" Then
                objX.Object.Caption = ""
            End If
        Next
    End With
End Sub

With Form Control Buttons, this works for me

Sub ClearCellsAndFormControlButtons()
    Dim ws As Worksheet:  Set ws = ActiveSheet
    Dim shp As Shape
    For Each shp In ws.Shapes
        ws.UsedRange.Clear
        If shp.Type = msoFormControl Then
            If shp.FormControlType = 0 Then
                ws.Shapes(shp.Name).Select
                Selection.Characters.Text = ""
            End If
        End If
    Next shp
End Sub

 

[ercedwards]

With Active-X Command Buttons, this works for me

Sub ClearCellsAndCommandButtons()
    Dim objX As Object
    With ActiveSheet
        .UsedRange.Clear
        For Each objX In .OLEObjects
            If TypeName(objX.Object) = "CommandButton" Then
                objX.Object.Caption = ""
            End If
        Next
    End With
End Sub

This is exactly the code I use and it works just fine.

The problem is that the screen does not update immediately after I clear the caption even with screenupdating=True and even when I Wait 2 seconds after each caption is cleared.
I need the screen to update immediately.

Thanks for any suggestions.

 

[Yongle]

Yes - the same thing happens on my PC and I have also tried a number of "tricks"
Why is immediate update essential?

 

[ercedwards]

Why is immediate update essential?[/QUOTE]

It's a visual thing only. When I run my "Daily Maintenance" routine, it runs through all of the orders in the system to update certain data.
As it runs, every order shows on the screen as it is being updated.

Since the ComboBox captions are not updating on the screen, the captions from the first order stay on the screen throughout the process but all of the other data on the screen changes as each order is loaded. All of the correct data is actually in all of the cells so there's no problem there.
It's just that it's confusing to the users when they keep seeing the same captions.

If I just run my ClearSheet macro and nothing else, all of the data and captions are properly cleared. However, the captions don't clear until a second or two after the screen has refreshed even though they are the first thing cleared.

It is really, really strange.

Adding in the WAIT did nothing but add an extra 1/2 hour to the processing time for Maintenance.

Oh well, I guess we just have to bear with it.

Cheers

 

[Yongle]

I will give you a couple of suggestions when I get back to my PC tomorrow - I want to test them first.

 

[ercedwards]

I will give you a couple of suggestions when I get back to my PC tomorrow - I want to test them first.

Sounds great.

I'm continuing to experiment my self as well.

 

[Yongle]

VBA appears to be very stubborn

Perhaps your best option is to switch off all screen updating until end of procedure and allow nothing to update
- bonus is that it runs faster for the user

Sub ClearCellsAndCommandButtons()
[COLOR=#000080]    Application.ScreenUpdating = False[/COLOR]
    Dim objX As Object
    With Sheets(1)
        For Each objX In .OLEObjects
            If TypeName(objX.Object) = "CommandButton" Then objX.Object.Caption = ""
        Next
           .UsedRange.Clear
    End With
    ....other code ....
[COLOR=#000080]    Application.ScreenUpdating = True[/COLOR]
End Sub

 

[ercedwards]

Stubborn doesn't describe it at times.

It still doesn't want to update the screen quickly.

 

[offthelip]

Just an idea: have you tried changing the visiblilty of the button False and then back to True to force a redraw of the button.