A couple of toughies...

Or, how do you recreate the Personal.xls file again?

I was thinking that I could put the open macro in the Personal.xls macro holder so it'd be available whether or not a book was open in excel. Am I thinking right and if so how do I recreate the Personal.xls macro holder (as mine's been deleted)

Cory

Hi Cory,

I believe you are using the built-in SaveAs dialog
called by:

Application.Dialogs(xlDialogSaveAs)

to save your workbook. I believe that if you switch to using the

filename = Application.GetSaveAsFilename(...

you will gain control of the Cancel button and not
lose your imported data when you run it. This is because this function only retrieves the file name and you have to do the subsequent SaveAs explicitly, so the SaveAs doesn't occur unless you want it to. The code would look something like:

Dim filename As Variant
filename = Application.GetSaveAsFilename(...

If filename <> False Then

ActiveWorkbook.SaveAs fname:=filename

EndIf

Which only saves the file if the user doesn't cancel.

I didn't understand your second question. Can you explain why the toolbar button macro code is changed? What changed it, and what did it change to?

Damon

Damon!

Thanks for responding. About the first part (saveas), I have been using what you suggested, which leads me to believe I've just been using it inappropriately. Here's the code (maybe by seeing it you could help point out the error):

Sub SaveAs()
Dim response
Dim Fs As String
response = MsgBox("Would you like to save before exiting?", vbYesNo, "Exiting PPSAR 2.1...")
'if 'no' then exit and close
If response = vbNo Then
frmMain.Hide
ActiveWorkbook.Close
Application.DisplayAlerts = True
Exit Sub
'here's where it saves...
Else
Application.DisplayAlerts = True
Fs = Application.GetSaveAsFilename("PPSAR " & Sheet1.Range("i1").Text & " 2001", fileFilter:="Microsoft Excel File (*.xls), *.xls")
'i think this is where the problem is...
If Fs = "False" Then
frmMain.Hide
Cancel = True
Else
ThisWorkbook.SaveAs Fs
Application.DisplayAlerts = False
ActiveWorkbook.Close
frmMain.Hide
Exit Sub
End If
Application.DisplayAlerts = True
End If
End Sub

And about the second part, i customized a 'macro' toolbar button and added it to my format toobar. I then assigned a macro to it that is supposed to open a workbook i created. works fine the first time, evrytime. here's a sample of the code:

Sub OpenPPSAR()
Application.DisplayAlerts = False
ChDir "U:\hpurc2\windows\Personal\Product Parameter Skipped Audit Report"

Workbooks.Open Filename:= _
"U:\hpurc2\windows\Personal\Product Parameter Skipped Audit Report\PPSAR.xls"
End Sub

First it changes directories to where the book is located. then it opens that book (PPSAR.xls).
what that book does is pop up a tiny form where i enter the name of another file and hit a 'start' button. that file is then imported, edited, and finally saved as a new book using the first code i gave you above. like i said, works great the first time...
Problem: after pressing the button on the toolbar and running that whole process, the workbook gets saved under a different name (which is how i need it so i can keep the original file as a template...). once the new file has been saved and everything has ended and been closed out, pressing the button on the toolbar causes excel to try and open the NEW file i saved, NOT THE ORIGINAL it's supposed to. Unless I reassign the old macro to it, it doesn't work! (follow that?)

And, last but not least (totally different problem), if i use the button to run the process, after everything's done, saved, and closed : the button automatically tries to run itself again! it's acting like once i press the button, it gets caught in a loop and keeps trying to run the whole process over and over again.

Thanks again!

Cory

Re: Damon!

Hi Cory,

First problem: Fs should be declared As Variant, because it has to be able to accept either a string value (the file name) or a boolean value (True/False, which is not the same as the string "True" or "False"). So your If test should also be changed to:

If Fs = True Then ...

Regarding the second problem, I can't see how the button can get assigned to a different macro. Have you examined the button to see what macro it is assigned to after the problem occurs? You can check this by right-clicking on the button while in Customize mode, and selecting Assign Macro. In addition, you should put a debug breakpoint
at the beginning of the OpenPPSAR procedure so that you can watch what happens when you click the button, and find out if it is getting executed. Since the filename is explicitly named, complete with path, I can't see how this macro can go wrong.

The third problem I believe is a bug in Excel. I have experienced it myself, and have never found a solution. I have not checked the Excel known bugs list at the microsoft.com website for this, but I would bet it is there. There may even be a workaround given.

Damon

Re: Damon!

Cory, does this help?

Sub SaveME()
Dim response As String
Dim Fs As Variant

response = MsgBox("Would you like to save before exiting?", vbYesNo, "Exiting PPSAR 2.1...")

If response = vbNo Then
ActiveWorkbook.Close False
Exit Sub
Else
Fs = Application.GetSaveAsFilename("PPSAR " & Sheet1.Range("i1").Text & " 2001", fileFilter:="Microsoft Excel File (*.xls), *.xls")
If Fs <> False Then
ActiveWorkbook.Close True
End If
End If

frmMain.Hide
Exit Sub

End Sub