'One-off' screen refresh (XL2007)

[AOB]

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...

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

 

[AOB]

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)

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]

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.

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!!

 

[AOB]

SOLVED!!

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