Update Modal UserForm

deepheat101

Board Regular
Joined
Jul 26, 2006
Messages
138
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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:
Code:
    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.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
1,136,992
Messages
5,679,018
Members
419,799
Latest member
APInfa

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