VBA to call UserForm for user input for use in same code

MichaelSchulz

Board Regular
Joined
Apr 10, 2014
Messages
64
Depending on the situation the value for a certain variable can be pulled directly from the workbook.
In other situations, it will be best to prompt the user to make a selection from values that are pulled from the workbook.

I have created a UserForm that displays under the right conditions and at the appropriate time, and it contains a ListBox that is properly populated.

What I do not know is how to structure the code that calls the UserForm such that it "waits", so to speak, for the user to make a selection and then continues.

Also, at this point in the code a number of values have been assigned to other variables and I do not wish to lose those values.

What is the best way to obtain user input while in the middle of the code?
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Generally speaking in most if not all cases when a UserForm is activated the script that launched the userform stops until you take some action in the UserForm.

So you could perform some tasks in your script. Then launch a UserForm that will give you the opportunity for User input before proceeding with the rest of the script.
 
Upvote 0
Generally speaking in most if not all cases when a UserForm is activated the script that launched the userform stops until you take some action in the UserForm.

So you could perform some tasks in your script. Then launch a UserForm that will give you the opportunity for User input before proceeding with the rest of the script.

That's what I had thought but it didn't turn out to be the case. The code that calls the UserForm continues right along.

I came across one idea:

Do Until UserForm.Visible = False
DoEvents​
Loop

And then also add a line to the CommandButton code, UserForm.Hide.

Not sure if this is a great solution but it does seem to work.
 
Last edited:
Upvote 0
The only way that can happen is if you launch your UserForm as modeless

If you launch your Userform like this:
UserForm.show
and do not have your UserForm defaulting as Show Model False

The script will stop

You ask the question and I gave you an answer but then you may want to not agree.
 
Upvote 0
The only way that can happen is if you launch your UserForm as modeless

If you launch your Userform like this:
UserForm.show
and do not have your UserForm defaulting as Show Model False

The script will stop

You ask the question and I gave you an answer but then you may want to not agree.

Let me test my understanding:

If the UserForm is launched as modeless, then the calling code will continue BUT if the UserForm is not launched as modeless, then the calling code will stop until the UserForm is done.

Is that correct?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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
Back
Top