Code to create and email PDF getting interupted

[Nitefox]

I am using the following code I found online to create and email a PDF of one of my sheets:

Please Login or Register  to view this content.

The code runs from a command button in a userform, and it works fine. However after clicking the command button if the user clicks anywhere in the workbook before the code completes then it stops the code from completing. The user is then left with a duplicate copy of the workbook open titled 'Workbook', and the main workbook is closed (which easily causes confusion).
Is there away I can prevent this from happening by allowing the user to click on the workbook without it interfering, or if this is not possible then somehow temporarily disable the mouse pointer and keyboard so the user is not able to accidentally interrupt it?

[6StringJazzer]

Is UserForm1 the form containing this button?

What is the "ShowModal" attribute of the form containing the button?

[Nitefox]

That is correct the button is located on UserForm1.
Im not sure what ShowModal is. How do I check that?

[6StringJazzer]

In the code development window, click on the line in the object browser for the form, then click F4. A Properties window will come up. However, the default is True, so if you are not aware of this then it is certainly still True. Which is what you want. If it were False, then the user could still do things in other windows while the form is showing.

You hide the form immediately upon pressing the button. I would suggest waiting until the end of the Sub to hide the form. That will prevent the user from action in any other >Excel< window.

[MarvinP]

I believe in "DoEvents" in code that should wait for other windows things to happen. Without the code waiting (DoEvents) it will keep firing instructions that are lost on the other program.

http://msdn.microsoft.com/en-us/libr.../gg264522.aspx

[Nitefox]

6StringJazzer, when I move the userform hide line to the end I get run time error '1004', Method 'Close' of object '_Workbook' failed, on the following line:
ActiveWorkbook.Close SaveChanges:=False

MarvinP, am I correct in understanding that 'DoEvents' allows the computer to run the task hidden in the background, allowing the user to still perform actions on the workbook without interfering with what its doing, or something of that nature?
If so how would I go about coding that?

[6StringJazzer]

I think that the OP's issue is the reverse. Nitefox wants to prevent the user from doing anything in a worksheet while the code is running, which then stops the code from running. DoEvents allows the OS to take control to execute any pending interrupts. DoEvents is frequently used in long-running loops to allow things like the display to be updated or respond to keyboard input.

I may be misunderstanding how you are suggesting to use DoEvents but I am not seeing how it will work in this case.

[Nitefox]

That is correct 6String, unless there is an easy way to allow the code to still work simultaneously to the user working on the workbook, then I feel the best way to get around this is to prevent the user from doing anything until the code has finished.

[MarvinP]

A few tests is how I'd go. Throw in some DoEvents in the code and see what happens.

My experience has been, I've needed them after opening a file that is large and takes a while to load. Just after the Open command I'd put in DoEvents which (I believe) would allow the Open command to complete before trying to toss in more VBA commands. As soon as another app (like a PDF maker) is working I need to tell VBA to wait until it is done before trying to do more commands.

I haven't spent much time looking at he code above and admire 6SJ and his experience. I'd simply throw in some DoEvents after Opens and see what happens.

[Nitefox]

I think I have a work around, I simply put the hide userform line right before the line that was causing the error before, and it seems to be working just fine now. The code after that line must work quick enough that the user doesn't have the chance to interrupt it (like they were if the hide userform line was first).
Thank you both for your help.