Update Modal UserForm

[deepheat101]

Hello All,

I have a UserForm (with "ShowModal = False" that happily allows me to update the the sheet beneath. As the updates will influence the data displayed on the UserForm, how do I get the user form updated when it regains focus or control?

Also how do I prevent the user from navigating to other tabs while the form is active (while still allowing updates to the sheet)

Many Thanks in advance

 

[Oorang]

Hmm don't know the "real" answer, but I know a workaround. The data updates itself when a control changes. So to code an update event you could do this in the event of your choice:

    On Error GoTo Err_Hnd
    Dim bytCntr As Byte
    Dim ctrl As MSForms.Control
    Dim ctrlTemp As MSForms.Control
    Dim boolDoEvnts As Boolean
    Set cntrl = Me.ActiveControl
    Me.MousePointer = fmMousePointerHourGlass
    Excel.Application.ScreenUpdating = False
    Me.Hide
    Me.Show Modal:=False
    boolDoEvnts = Application.EnableEvents
    If boolDoEvnts Then Application.EnableEvents = False
    For Each ctrl In Me.Controls
        ctrlTemp.SetFocus
        bytCntr = bytCntr + 1
        If bytCntr = 2 Then Exit For
    Next ctrl
    ctrl.SetFocus
Exit_Sub:
    If boolDoEvnts Then Application.EnableEvents = True
    Me.Show Modal:=True
    Excel.Application.ScreenUpdating = True
    Me.MousePointer = fmMousePointerDefault
    Exit Sub
Err_Hnd:
    MsgBox Err.Description, vbCritical, "Error: " & Err.Number
    Resume Exit_Sub

I know this is kind of kludgy so you might ask around to see if there is a better way.

 

[deepheat101]

Many thanks for looking - I'll give it try

 

[Richard Schollar]

Hi

This is completely untested and may very well not work, but does the Repaint command cause the data to be updated? Hmm, it seems to suggest it would in the VBA Help.

Richard