Make a Modal userform act modeless or vice versa, whichever is easiest.

Hi Everyone,

I am writing a macro in Excel 2007 and all is working well except for one sticking point. The macro screen scrapes information from a hummingbird session and posts it on a spreadsheet. Once it has a few key pieces of information from the system, it requests some info from the user, i.e. customer name, division, and administrator the customer is assigned to. To limit the number of input errors, the administrator name is set up as a userform with a combobox. I also want to set up a similar userform/combobox for division.

Here is my issue. The user needs to be able to search the active spreadsheet, or a completely different spreadsheet. If I set the userform as modal, I can't search the spreadsheet. If I set it as modeless, it doesn't wait for a response to continue, and the info posted in that combobox is vital to the continued processing of the macro.

So how can I set a modal userform to allow me to click off and focus on the spreadsheet and then enter the info when it is found. Otherwise, how do I make a macro show a modeless userform and wait for info to be input before continuing.

I'm reasonably new to VBA and am more or less self taught so I'd hope to find a fairly simple method to do this, but none of the forums I have searched have addressed my particular issue.

Thank you in advance for your help.

Welcome to the forum. :)

My suggestion -

Your initial macro can just show the form modelessly, nothing else.

The remainder of the code can be executed (be called) when the user clicks on a 'submit' button (or similar) on the userform.

Thanks for the help. How would I code this? Would this be written into the macro code or into the userform code? I tried to create this userform modelessly but when it ran it displayed the form, then attempted to continue along, getting an error message because the info required to do the next step needs to be input in the userform.

Hi,

Let's use a simple example to demonstrate.

Open a new Excel workbook.
Insert a Userform and add a commandbutton to it.

Then add a standard code module and add this code to it:

Sub Test()

UserForm1.Show vbModeless

MsgBox "Done"

End Sub


If we then run the Test() sub, the userform is shown but we also see the messagebox. I think this emulates the scenario you described: we only want the messagebox to show when the button on the userform is clicked.

Amend the Test() procedure to this:

Sub Test()

UserForm1.Show vbModeless

End Sub

Then go back to the userform and double click on the commandbutton. The default event handler for a commandbutton is its Click event handler, so some code is automatically added to the userform's class module for you:

Private Sub CommandButton1_Click()

End Sub


Now amend that code as follows:

Private Sub CommandButton1_Click()
MsgBox "Done"
End Sub


Now go back to the Test() procedure and run it.
You will now see that the userform is shown but it is only when you click on the command button that the "Done" message is shown.

So by using event handlers of the controls on the userform we can show it modelessly and ensure that the remainder of the code isn't executed until the relevant actions on the userform have been performed.

Hope that helps...

I think that will help greatly. Thank you very much. I'll give your suggestions a try and hopefully all will work out.