Excel macro question

[peobody]

I've used a macro to automate functions in a purchase order form that our company uses. The macro opens a template (VBAProject1) and saves it to %temp% for further processing. Once the user finishes the form I invoke a macro from a separate project (VBAProject2) which removes all modules and objects from VBAProject1temp, saves it on the server then deletes the temp file. The last thing VBAProject2 does is to display a message box asking the user if they want to create another purchase order. If yes it starts VBAProject1 again otherwise it exits. My problem is that while this message box is being displayed another user cannot start VBAProject1. The best I can tell VBAProject1 should only be active only for the brief moment that it's saving a copy of itself in %temp%. So why is it locked while the message box from VBAProject2 is waiting for a response?

TIA!
Peobody

 

[VBAjedi]

You don't list your code, so we can't say for sure. However, if you are truly saving a copy (with the SaveCopy(?) method), then you probably still have the original document open. Of course, if that's the case, nobody else should be able to open Template1 the entire time the user is filling out the form.

Post your code. . . particularly the part where you "save a copy".

VBAjedi

 

[peobody]

Part of what is so strange is that other users ARE able to open Template1 while another users is working on a form.

Here is the code where I save the template:
*******
' Save new PO workbook as temp file for further processing
strTempFilename = Range("aa2") & "_tmp"
Application.EnableEvents = False
ThisWorkbook.SaveAs filename:=strTempPath & "\" & strTempFilename, _
FileFormat:=xlNormal, ReadOnlyRecommended:=False
Application.EnableEvents = True
On Error GoTo 0

Call GetUserName

Range("E9:S9").Select
Application.ScreenUpdating = True

Exit Sub
********

This is the code I use in VBAProject2 that starts Template1(VBAProject1) if the user wishes to create another purchase order:
********
varRepeat = MsgBox("Do you wish to create another Purchase Order?", vbYesNo, "")
If varRepeat = vbNo Then
ThisWorkbook.Close Savechanges:=False
Exit Sub
Else
varNewPOForm = strWpath & strPOFile
Application.OnTime Now, varNewPOForm & "!Autpen"
Application.ScreenUpdating = True
ThisWorkbook.Close Savechanges:=False
End If

End Sub
********

 

[VBAjedi]

Ok, several notes here:

Be aware (if you aren't already) that closing a workbook with code that resides within the same workbook can be very tricky. It can be the source of all kinds of problems, and could easily be a part of your issue here.

Also, I'm suspicious of the line:

Application.OnTime Now, varNewPOForm & "!Autpen"

If the workbook represented by varNewPOForm has code in the Autpen event, that code should run without any assistance when the workbook is opened. So you should just be able to open the workbook and let the code fire on its own. Or did that cause some other problem for you?

Could you instead put code in the template1 file that checks for the second file, and closes it if found? It's much easier to close a workbook with code in another workbook.

VBAjedi

 

[peobody]

>Be aware (if you aren't already) that closing a workbook with >code that resides within the same workbook can be very >tricky.

To me this means that a 2nd file would be required to close the 1st, a 3rd to close the 2nd, ad infinitum. One of my objectives is to not leave any workbooks open once the user says he's done. How would that be accomplished if the subroutine that asks "Are you done?" can't close its workbook?

Peobody

 

[VBAjedi]

I didn't say it couldn't be done, just that it can be tricky. It's been a while since I had to mess with it, but I seem to remember a major qualifier being that you couldn't have ANY code slated to execute after the workbook close statement. That means that a) the close statement is the last statement of the module it's in, and b) you can't have code in any event that will be triggered by the close (the BeforeClose event being the most obvious one to avoid).

I think I ended up using an Application.Ontime statement to call a sub with only one line (the close line) after waiting several seconds (so the currently running code can finish up).

If you're interested in this approach, do some searching through the forums for more info.

VBAjedi