MrExcel Publishing
Your One Stop for Excel Tips & Solutions

using the implied buttons in open and save dialogs


Posted by Luke on November 07, 2001 7:40 AM

In my programs I use the 'getsavefilename' etc, but I cannot seem to find the right command to test if the user has aborted the procedure himself, escpecially in the open and save commands, but also in other vba supplied routines. How can I find out, using these command within modules is the user has pressed the abort key or the exit X in the corner etc. The are query subs, but that doesn't seem to work


Posted by Barrie Davidson on November 07, 2001 10:51 AM

If the user cancels the "GetSaveAsFileName", it will return False. You could use Error Trapping like this.

GetName:
savefilename = Application.GetSaveAsFilename
If savefilename = False Then GoTo GetName


Regards,
BarrieBarrie Davidson

Posted by Luke on November 07, 2001 12:50 PM

I use this routine in very much the same way:

Do
fname = Left(ActiveWorkbook.VBProject.Description, 3) & "_"
fname = fname & Year(Worksheets("personeels berekeningen").Cells(7, 5)) + 1
fname = Application.GetSaveAsFilename(fname, fileFilter:="Excelbestanden (*.xls), *.xls")
If fname = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then
MsgBox ("Deze bestandsnaam '" & ActiveWorkbook.Name & "' is al in gebruik door een actief bestand en mag niet gebruikt worden")
fname = False
End If
Loop Until fname <> False

the problem with this routine is that I want to catch incomplete and incorrect filenames using the fname <> false function, but if the user cancels using either cancel button or the cancel X I also want to know so I can cancel the routine.
Basically like using forms, where you can use the userform_query function and get values in return which correspond to the different actions of the user.

Luke

Posted by Luke on November 07, 2001 12:52 PM

Sorry, it is a dutch program, the messagebox tells the user that the filename is already in use and cannot be accepted.
Luke

Posted by Barrie Davidson on November 07, 2001 12:56 PM

You could change your code to

Do
fname = Left(ActiveWorkbook.VBProject.Description, 3) & "_"
fname = fname & Year(Worksheets("personeels berekeningen").Cells(7, 5)) + 1
fname = Application.GetSaveAsFilename(fname, fileFilter:="Excelbestanden (*.xls), *.xls")
If fname = False Then Exit Loop
If fname = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then
MsgBox ("Deze bestandsnaam '" & ActiveWorkbook.Name & "' is al in gebruik door een actief bestand en mag niet gebruikt worden")
fname = False
End If
Loop Until fname <> False

I have only added one line:

If fname = False Then Exit Loop

which will exit your loop if the user cancels the GetSaveAsFileName.

Does this help you out?

BarrieBarrie Davidson

Posted by Barrie Davidson on November 07, 2001 1:02 PM

Okay, I just re-read your code and realized that I missed the point. Try adjusting your code to something like:

Do
fname = Left(ActiveWorkbook.VBProject.Description, 3) & "_"
fname = fname & Year(Worksheets("personeels berekeningen").Cells(7, 5)) + 1
GetName:
fname = Application.GetSaveAsFilename(fname, fileFilter:="Excelbestanden (*.xls), *.xls")
If fname = False Then
MsgBox ("You must select a file to open")
GoTo GetName
End If
If fname = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then
MsgBox ("Deze bestandsnaam '" & ActiveWorkbook.Name & "' is al in gebruik door een actief bestand en mag niet gebruikt worden")
fname = False
End If
Loop Until fname <> False

I added:

GetName:

and I added:

If fname = False Then
MsgBox ("You must select a file to open")
GoTo GetName
End If

Does this help you out?

Barrie
Barrie Davidson

Posted by Luke on November 07, 2001 1:03 PM

I think it would solve half the problem. Wouldn't this also exit the loop if the user does not enter a file name since a blank name is also a false name?

Posted by Luke on November 07, 2001 1:10 PM

Hate to be a pain, but using this getname loop to catch blanks, we're back to square one, I think, if I understand it correctly, because pressing the cancel buttons would also result in a false name and thus also return to the getname loop

Posted by Barrie Davidson on November 07, 2001 1:17 PM

You're absolutely correct. Actually, you can eliminate the loop (if I understand your requirements) by something like this:

GetName:
fname = Left(ActiveWorkbook.VBProject.Description, 3) & "_"
fname = fname & Year(Worksheets("personeels berekeningen").Cells(7, 5)) + 1
fname = Application.GetSaveAsFilename(fname, fileFilter:="Excelbestanden (*.xls), *.xls")
If fname = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then
MsgBox ("Deze bestandsnaam '" & ActiveWorkbook.Name & "' is al in gebruik door een actief bestand en mag niet gebruikt worden")
fname = False
End If
If fname = False Then
MsgBox ("You must select a valid file name to save this file")
GoTo GetName
End If

Am I in the ballpark here or still standing in left field?

BarrieBarrie Davidson

Posted by Luke on November 07, 2001 1:55 PM

You're absolutely correct. Actually, you can eliminate the loop (if I understand your requirements) by something like this:

I am going to try it and see where I get. If it doesn't work I will post again, but I have to socialize with my family. In Holland it is now past midnight. At a first glance, I now again miss the possibility of checking the buttons, but I am going to try. Thanks for the time you put in up til now.
Luke