Automatically execute some code after drawing UserForm
I created a UserForm where the user has to fill in three fields. The macro tries to automatically detect the values of these fields in the event UserForm_Initialize()
, and then displays the found values in three fields, but the user can change them. However, automatic detection takes a few seconds and delays the appearance of the UserForm. I would like the UserForm to be displayed with fields before auto-detection routine, then auto-detect the fields automatically. What would be the best way to do this? Creating a modeless UserForm makes the macro run without waiting for user input, which is problematic. I don't want to have an "auto-detect" button: it needs to be done automatically.
source to share
Use Activate()
event instead Initialize()
:)
Private Sub UserForm_Activate()
End Sub
Followup
Thank! It works, but there seems to be a bug: the dialog is drawn completely white until the macro finishes. screenshot (dialog should be grayed out)
Not. It's not a mistake. Try it. Add Doevents
as shown below.
Private Sub UserForm_Activate()
UserForm1.ProgressBar1.Value = 0
starttime = Timer
While Timer - starttime < 1
UserForm1.ProgressBar1.Value = (Timer - starttime) * 100
DoEvents
Wend
End Sub
NTN
Sid
source to share
I would suggest using a timer. Open a form with disabled and empty input fields and set the timer to fire for a few hundred milliseconds. This should render the form immediately. Make your auto discovery during the timer event (disable the timer first), then enable the fields and fill in the detected values.
source to share
There is an easier way to do this ...
1) On your user form, create a new "CommandButton" that will execute the macro you want to call.
2) Set the height and width of the button to 0
3) Make sure the "TabIndex" parameter for the button is 0 ... This will create an "invisible" CommandButton that will receive focus as soon as the form opens.
4) In the calling procedure, just before the command that "shows", the userform enters the line - "Application. SendKeys" ~ "'
How it works...
The command line created in (1) is a valid control just like any other if you don't see it or click it with your mouse. The SendKeys command replicates the left click that is stored in the keyboard buffer until the form appears when it is read. This has the same effect as clicking the mouse and will run the required macro.
By the way, if you are calling a macro from multiple locations and want to have different actions depending on the source of the call, you can add multiple "invisible" buttons and add "{Tab}" before ~ "to insert focus through the available controls. "Application.SendKeys" {Tab} ~ "" activates the button with the 'TabIndex' parameter set to 1. 'Application.SendKeys' {Tab} {Tab} {Tab} {Tab} ~ "" activates the button with the "TabIndex" parameter set as 4, etc.
RF
source to share