'One-off' screen refresh (XL2007)

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
598
Hi,

Does anybody know if it's possible to do a 'one-off' screen refresh?

I have a macro which takes a bit of time to run so I turn the ScreenUpdating off for the duration, then flick it back on at the end.

However, under certain conditions, it's necessary to prompt a user mid-run to enter some data via a form, and as my macro is passing keys to another application (which may have overlaid the screen and then been minimised) this sometimes means the pop-up form is not immediately obvious to the user (they have to click on the workbook in the taskbar to bring focus back to the workbook, where the form is waiting)

I tried adding a line to re-activate the workbook prior to showing the form but it doesn't seem to have any effect at run-time. Is there a suitable line that I can add to quickly refresh the screen but retain the general mode of no ScreenUpdating?

(I read on MSDN of Application.ScreenRefresh but the compiler doesn't recognise it? Is it only applicable to Word?...)

Or do I just have to switch ScreenUpdating back on, then switch it back off again (seems a bit silly?...)

Shortened version of my code here if it aids understanding...

Code:
Function myFunction(Optional myParameter As String) As Boolean[INDENT]
Application.ScreenUpdating = False

' Function code here...

ThisWorkbook.Activate     ' This doesn't seem to have the desired effect...
myUserForm.Show           ' ...so this form doesn't visibly pop-up

' ...more code...

Application.ScreenUpdating = True


[/INDENT]
End Function
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
598
Further to the above - I found that calling DoEvents removed the 'shadow' caused by the other application's window briefly inhabiting the screen. So the workbook becomes the visible window again, but doesn't have the main focus (i.e. the form is initialised, but still hiding 'in the background' - the user has to return focus to the workbook, i.e. click on the taskbar or Alt-Tab to the workbook, to notice it)

Code:
Function myFunction(Optional myParameter As String) As Boolean[INDENT]
Application.ScreenUpdating = False

' Function code here...

ThisWorkbook.Activate
[COLOR=#ff0000]DoEvents
[/COLOR]myUserForm.Show

' ...more code...

Application.ScreenUpdating = True


[/INDENT]
End Function

How do I get the userform to sit visibly on top of the workbook???
 

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
598
Closer & closer...

This code brings my userform to the fore!

BUT!! It still doesn't have the primary focus, so keystrokes do not appear in the textbox. You still have to either click into the form or Alt-Tab to get the focus back on the userform first!! Also, the shadow of the now-minimised 'other application' has returned.

Code:
Function myFunction(Optional myParameter As String) As Boolean[INDENT]
Application.ScreenUpdating = False

' Function code here...

[COLOR=#ff0000]DoEvents
AppActivate myUserForm.Caption
myUserForm.myFirstTextBox.SetFocus
myUserForm.Show
[/COLOR]
' ...more code...

Application.ScreenUpdating = True


[/INDENT]
End Function

Please can somebody help me, this is doing my nut!! :oops:
 

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
598
SOLVED!!

Code:
Function myFunction(Optional myParameter As String) As Boolean


[INDENT][COLOR=#ff0000]Dim ExcCap as String
[/COLOR]Application.ScreenUpdating = False
[COLOR=#ff0000]ExcCap=Application.Caption

[/COLOR]' Function code here...

[COLOR=#ff0000]AppActivate ExcCap                      ' Return focus to Excel[/COLOR]</SPAN>[COLOR=#ff0000]

Application.ScreenUpdating = True       ' Switch ScreenUpdating back on...
[/COLOR]</SPAN></SPAN>[COLOR=#ff0000]Application.ScreenUpdating = False      ' ...and switch it back off again[/COLOR]</SPAN></SPAN>[COLOR=#ff0000]
[/COLOR]</SPAN>[COLOR=#ff0000]
myUserForm.Show                         ' Show the user form

[/COLOR]' ...more code...

Application.ScreenUpdating = True


[/INDENT]
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

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