Text boxes not being updated while VBA sub active

1
votes

I have a large cell in sheet "main" containing several text controls. These are linked to named cells in sheet "Refs". There's a button in "main" that launches a VBA sub.

At the start, the sub does...

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

then it updates the cells behind the names in sheet "Refs" Each update is surrounded by

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

RefsSheet.Cells(row_downldstat, col_downldstat) = state

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

The VBA sub takes a while to finish so the idea is to provide feedback to the user as to what's going on. Unfortunately the text boxes are not updated until the VBA sub returns.

If I put the formula =Down_State in a cell on the "main" sheet I see it updated as the VBA routine runs. Down_State is the same cell as RefsSheet.Cells(row_downldstat, col_downldstat). I prefer NOT to use this method as there are several textboxes and the whole idea was to migrate from such a design.

Note that the textboxes are simply in a cell, not in a form.

I do know about the application.statusbar but that's not very noticeable.

How can I cause these text boxes to be updated while the VBA sub is active?

2
vbaexcel
You can display the updates in a cell below.. For Example "Please wait while the data is being processed..." or a customized message so that the user knows what's going on... Once the sub finishes processing, clear the contents of that cell and update the textboxes. – Siddharth Rout
OR use DoEvents after you update the Down_State cell so that excel gets time to update the textbox. – Siddharth Rout
Have you tried forcing an update by using "tb.caption = range("A1").value" types of code? This may be useful depending on your implementation and spreadsheet design. – enderland
@Siddharth Rout: I added a doevents but it didn't help. – Mike D
Is it possible for me to see your file? – Siddharth Rout

2 Answers

1
votes

Change your UpdateStatus to this.

Sub UpdateStatus(state As String)
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    RefsSheet.Cells(row_downldstat, col_downldstat) = state

    Wait 1

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Application.DisplayStatusBar = True
    Application.StatusBar = state
End Sub

And add this new sub below the above.

Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While nSec > Timer
        DoEvents
    Wend
End Sub

Now Test it ;)

0
votes

I'm not sure if this is exactly what you're looking for:

you said

the idea is to provide feedback to the user as to what's going on

If you can not get the cells to update, perhaps creating a userform that comes up while the code runs could be useful.

here is a link to a type of userform that you could use, which simply displays a progress bar (this is most easily utilized when you are doing a lot of looping, but can be used in linear code as long as you don't care about the percentage accuracy)

http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/

From the link:

    PctDone = Counter / (RowMax * ColMax)
    With UserForm1
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
    End With

' The DoEvents statement is responsible for the form updating DoEvents

if you want you can edit the userform and the code to simply update the 

   .frameprogress.caption =format(pctdone, "0%")

to

   .frameprogress.caption = "String of desired text"

This is a way you can give the user feed back that a sub is running however it may not be optimal for your needs