Userforms in Excel - how do I pause a maro?

[Kevinbould]

Hi, I am writing a few macros that for different reasons require that the user select a letter at the very beginning of the macro. This letter will then be used in a variable within the rest of the macro.

In macro one, a number of users are mapped to the same network drive but with the drive stored on different letters. They need to select which letter the drive is stored on, so that a vlookup will work correctly. I have tried mapping to the network path, but couldn't get this to work. I can get the variable to work if I enter the letter in a cell on the spreadsheet, but this isn't viable for the users. As such a simple userform prompting for the letter needs to pop up. I can do this, but in the meantime the macro carries on running. I need to pause the macro until the command button is pressed.

For the second macro, a search needs to be carried out to start a chain of events in the macro. The search could be on one of any number of columns. Same problem as above.

Please help

Thanks

[si_the_geek]

You just need to tell the form to be Modal (dont pass back control to the caller until the form has closed), eg:

rather than:
UserForm1.Show

use this:
UserForm1.Show vbModal

[Kevinbould]

Easy and effective. Cheers mate. I had kinda found a long complicated way around this, but yours is so much simpler! Cheers