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

MichaelSchulz

New Member
Joined
Apr 10, 2014
Messages
26
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:

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,263
Office Version
  1. 2013
Platform
  1. Windows
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.
 

MichaelSchulz

New Member
Joined
Apr 10, 2014
Messages
26
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:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,263
Office Version
  1. 2013
Platform
  1. Windows
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.
 

MichaelSchulz

New Member
Joined
Apr 10, 2014
Messages
26

ADVERTISEMENT

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,291
Messages
5,600,760
Members
414,405
Latest member
Zaurb

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