How does the "vbModeless" in "UserForm.show vbModeless" work?

Hello,

How does the "vbModeless" in "UserForm.show vbModeless" work? I have more code after this line, and my guess that it runs the rest of the code until there is none left, at which point the user is free to work on both the worksheet and userform. Am I right? (Before I added "vbModeless", the code would run after I used the userform)

Now, is there a way to make the program from not continuing to run after that vbModeless is run?


Thank you!

Check the "Show Method" in Excel VBA help. In short, "Modal" prevents any other execution until user input is received; whereas, "Modeless" allows subsequent code to be executed.

No need to apologize for clarifying. Here is what is says in the Help file:

When a UserForm is modal, the user must respond before using any other part of the application. No subsequent code is executed until the UserForm is hidden or unloaded. Although other forms in the application are disabled when a UserForm is displayed, other applications are not.

I was paraphrasing. Perhaps this is simpler: Modal "pauses" execution until user responds. Modeless does not.

I'll need to see the whole sub to be sure of what I'm looking at. But, if I'm following any part of this correctly, the user form will show but the rest of the code is reached before the user has a chance to input anything. If user input is needed/desired, I'd prompt the user and only give them the option of adding or canceling. If they add, do whatever is needed next. If they cancel, exit the sub (or whatever you want to happen).

if y and the next available are the same, then the user did not add a new entry, suggesting user is done

Generally not a good idea to rely on your code making assumptions. Give the user 2 (or more) options and explicitly test for the user's choice. Black/white. True/False. 0/1. Etc. Leave no room for error.

You are possibly calling the procedure ShowAddNewEntry recursively. This simply means the procedure is calling itself. While this is valid programming construct it should be used carefully and I don't think this is a suitable situation to use recursive calls.

In my opinion, you need to review the structure of your code... (and that'll be another apology )

Haha I figured I would! Although before adding the "vbModeless" it was running perfectly!

If you guys are up for the challenge, I'll leave a copy here (without any entries and many of the sheets, for security reasons).

I am open to any and all suggestions to improve my code!

Perhaps if you explained why you want to show the userform Modelessly...?

It is used to add new information, so should (my opinion) be modal, blocking access to the worksheets and other elements until entry is complete...

I can't download your file at my current location (nothing wrong with the file, just not authorized). I'll try to take a look later tonight, if cytop hasn't already solved. Like cytop, though, I'm curious, if

before adding the "vbModeless" it was running perfectly

, what did you hope would happen when you made it modeless?

First, for a little more context, the programming checks if a PN has already been entered into either worksheet. If it has, it will copy all the information (resourcing package, vendor name/code etc...) associated with that PN

If it hasn't (aka, a new PN), the userform will pop up. The information for the new PN may be found in another file. I thought it would be easier for the user to be able to copy-paste the required info from the file into the input fields if the userform was modeless

So, it was working perfectly in the sense that everything was running sequentially, at the time it was supposed to

Also, in response to an expected reply, no, I cannot easily have a piece of code check if the PN exists in the other file and have all the necessary info copied. The other file, which does not belong to my team, is updated daily and everytime you open it, a _# (# = number of times you opened it that day) is added. On top of it, not everyone needs the file opened. For the sake of coding and user simplicity, it would seem like a hassle to have code for this

EDIT (kind of)
...I'm rereading this last paragraph I wrote and realized perharps I could code it with something like:
for n = 1 to 10
'string = file name _n
'check if open
'copy values

'else exit sub open userform


hmm, i think that could work

Okay, I can see I'm not going to have time to get into your code today. I know what you need, though (at least I think I do). After you launch the entry form (modeless), you need a do/while loop to hold the macro while user either fills in the form and clicks okay, or clicks cancel on the form. Never really dealt with modeless form. You may need to use the form to set a public property. Something like (below not tested or expected to be thorough).

Please Login or Register  to view this content.

That's the idea. Modeless allows user to access other file. Do/While prevents code from going any further until user is done. So you get both modeless and modal behavior.