Save Macro - using inputbox

SOSVBA

Board Regular
Joined
Aug 30, 2009
Messages
210
Objective:
I have a workbook consisting of several worksheets that users input various data. I have a command button (SAVE button) and I want after the user inputs the filename to have the option to close the workbook and exit or have it remained opened. The file will be saved in the same directory that had the original file.

I have the following code:
Sub Save_Click()
'To save file and provide option to close the file
Dim FileName1 As String
Dim Close1 As String

FileName1 = InputBox(Prompt:="Please enter file name")
If FileName1 = "" Then
MsgBox Prompt:="You did not enter a filename", Buttons:=16
FileName1 = InputBox(Prompt:="Please enter file name")
End If
ActiveWorkbook.SaveAs Filename:=FileName1
Close1 = MsgBox(Prompt:="Do you want to exit the file?", Buttons:=4)
If Close1 = vbYes Then ActiveWorkbook.Close
End Sub


Issues:
1. The file does not save with the xls extension. How do I incorporate code so that it does. Also I don't the user to input the extension. If they do, is there some way to ignore it so that the filename does not take on the format as ABC.xls.xls?

2. If the user were to input the same filename when the save button is selected and is prompted by excel if they want to replace the existing file and if no or cancel is selected - the following error is displayed

Run-time error '1004':
Method 'SaveAs' of Object '_of Workbook failed.

When I hit debug, the following line is highlighted in yellow? Why?

ActiveWorkbook.SaveAs Filename:=FileName1


Help is greatly appreciated.

Thank you.
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Objective:
Issues:
1. The file does not save with the xls extension. How do I incorporate code so that it does. Also I don't the user to input the extension. If they do, is there some way to ignore it so that the filename does not take on the format as ABC.xls.xls?
Add the '.xls' to the filename string when saving.

2. If the user were to input the same filename when the save button is selected and is prompted by excel if they want to replace the existing file and if no or cancel is selected - the following error is displayed

Run-time error '1004':
Method 'SaveAs' of Object '_of Workbook failed.

When I hit debug, the following line is highlighted in yellow? Why?

ActiveWorkbook.SaveAs Filename:=FileName1
Because that is the line of code being executed when the error occurred. You need to add code to account for that error.


This code should take care of the inputbox issue. Please note that the below code is OVERWRITING any files if a file with the same name already exists. It is simply not displaying that dialog and continuing on to save the file. It can be reworked if you want to give the user the option of renaming or canceling the save, though.
Code:
Sub Save_Click()
Dim FileName1 As String

'show inputbox for filename until user clicks cancel or enters a filename
Do
    FileName1 = Application.InputBox("Please enter file name", Type:=2)
    If FileName1 = "" Then MsgBox "You did not enter a filename", vbCritical
    If FileName1 = "False" Then Exit Sub 'if clicks cancel, end macro
Loop While FileName1 = ""
        
'remove instance of '.xls' if it was added by user
FileName1 = Replace(FileName1, ".xls", "")

'save workbook with .xls extension
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=FileName1 & ".xls"
Application.DisplayAlerts = True

If MsgBox("Do you want to exit the file?", _
vbYesNo + vbQuestion) = vbYes Then ActiveWorkbook.Close

End Sub
 
Upvote 0
There may be an 'easier' way to go about it, but the only way I could figure to 'trap' the clicks of the dialog that appears when the file already exists was to just display my own and code accordingly.

Code:
Sub Save_Click()
Dim fName As String, fPath As String
Dim Chk As Variant

fPath = ActiveWorkbook.Path

'show inputbox for filename until user clicks cancel or enters a filename

Name_1: Do
    fName = Application.InputBox("Please enter file name", Type:=2)
    If fName = "" Then MsgBox "You did not enter a filename", vbCritical
    If fName = "False" Then Exit Sub 'if clicks cancel, end macro
Loop While fName = ""
        
'remove instance of '.xls' if it was added by user
fName = Replace(fName, ".xls", "")

'check if file already exists
If FileExists(fPath & Application.PathSeparator & fName & ".xls") Then
    'if file exists, show dialog and capture results
    Chk = MsgBox("A file named '" & fName & ".xls' already exists in this location." _
    & vbcrlf & "Do you want to replace it?", vbYesNoCancel + vbInformation)
    If Chk = vbCancel Then Exit Sub 'if cancel clicked, end macro w/out saving
    If Chk = vbNo Then GoTo Name_1 'if no clicked, go back to loop and allow user to enter new filename
End If

'if yes, code continues here and saves file

'save workbook with .xls extension
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=fName & ".xls"
Application.DisplayAlerts = True

If MsgBox("Do you want to exit the file?", _
vbYesNo + vbQuestion) = vbYes Then ActiveWorkbook.Close

End Sub

Private Function FileExists(FileName) As Boolean
'   Returns TRUE if the file exists
Dim x As String
x = Dir(FileName)
If x <> "" Then FileExists = True _
    Else FileExists = False
End Function
 
Upvote 0
Kristy,

It works just fine. You're the best!!!

By the way, the only minor request is that is it possible to write a code that will remove xls if added by the user in any case format (i.e. upper or lower case). That is if the user were to name a file Test.XLS or Test.Xls or Test.XLs,....(all the permutations/combinations of upper and lower cases of xls), that the xls would be removed. Right now, only "xls" is removed but not any combinations of upper or lower cases of "xls" is not removed.

Also, one last review of code...

I have exit/close button - code is as follows:

Sub eXIT_Click()
' To close the file
Dim Ans As String
Ans = MsgBox("Are you sure you want to exit the file?", vbYesNo)
If Ans = vbYes Then ActiveWorkbook.Close

End Sub


It seems to work fine - the only thing is that the cursor is in its hour glass mode but when you hover it over the message box the hour glass disappears and the cursor reappears so that you can click on yes or no buttons. My concern is that the user will think the program is in its thinking mode when the cursor is being displayed as a hour glass and will not hover it over the message box to make the selection. Any reason as to why it is in its hour glass state? Is it caused by the code above?

Last question - I defined the exit button when I clicked on assign macro and it adjusted it as eXIT. Reason?
 
Upvote 0
Kristy,

Follow-up 1

I modified the excellent code that you had provided to incorporate the option of allowing the user to save the same filename - thereby, eliminating the repetitive task for the user of inputting the same filename.
The changes are highlighted in red.
Your thoughts on the approach listed below would be greatly appreciated.

Rich (BB code):
Sub Save_Click()
Dim Ans As String
'show inputbox for filename - user can click cancel, keep existing filename or _
    create new file name
   Ans = MsgBox("Do you want to save the file under the same file name?", _
     vbYesNoCancel + vbQuestion)
 Select Case Ans
     Case vbYes
            ActiveWorkbook.Save
                If MsgBox("Do you want to exit the file?", vbYesNo + vbQuestion) _
                    = vbYes Then ActiveWorkbook.Close
                    Exit Sub
         Case vbNo
         DifferentName
      Case vbCancel
         Exit Sub
     End Select
End Sub
 
 
Sub DifferentName()
Dim fName As String, fPath As String
Dim Chk As Variant
fPath = ActiveWorkbook.Path
'inputbox for filename until user clicks cancel or enters a filename
Name_1: Do
    fName = Application.InputBox("Please enter file name", Type:=2)
    If fName = "" Then MsgBox "You did not enter a filename", vbCritical
    If fName = "False" Then Exit Sub 'if clicks cancel, end macro
Loop While fName = ""
 
'remove instance of '.xls' if it was added by user
fName = Replace(fName, ".xls", "")
'check if file already exists
If FileExists(fPath & Application.PathSeparator & fName & ".xls") Then
    'if file exists, show dialog and capture results
    Chk = MsgBox("A file named '" & fName & ".xls' already exists in this location." _
    & vbCrLf & "Do you want to replace it?", vbYesNoCancel + vbInformation)
    If Chk = vbCancel Then Exit Sub 'if cancel clicked, end macro w/out saving
    If Chk = vbNo Then GoTo Name_1 'if no clicked, go back to loop and allow user to enter new filename
End If
'if yes, code continues here and saves file
'save workbook with .xls extension
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=fName & ".xls"
Application.DisplayAlerts = True
If MsgBox("Do you want to exit the file?", _
vbYesNo + vbQuestion) = vbYes Then ActiveWorkbook.Close
End Sub


Follow-up 2

I copied the code for closing and exiting the file above for the exit button:

Rich (BB code):
Sub eXIT_Click()
' To close the file
If MsgBox("Do you want to exit the file?", _
vbYesNo + vbQuestion) = vbYes Then ActiveWorkbook.Close
End Sub

I thought I would do so because I wasn't sure as per my last reply as to whether my coding was causing that hour glass cursor issue. However, not only is it still there but I also notice that it happens when I select the Save button under follow-up 1 above.

Do you know why that hour glass cursor happens? I'm afraid it will give the user the uneasy feeling that the file is in a wait pattern and that they cannot move on to the next task of clicking buttons or filing out the form. Any way of fixing it?

Thank you for your help
 
Upvote 0
Kristy,

One last question - because I have the SAVE and EXIT command buttons on the spreadsheets (also PRINT command buttons), I don't want the user to be able to do these functions through the excel toolbar - that is, going through File/Save or File/Exit or X or File/Print....

Is there any way to deactivate those functions and alert the user if they try to do so that they have to hit the respective buttons on the spreadsheet. That is if they were to click File/Save, they will be denied and be alerted or directed to the SAVE command button.

Thank you in advance.
 
Upvote 0
Von Pookie,

I have an issue with the code provided and I can't seem to find a solution in my research. I would like the file to save in the directory that the file is originally saved in. Therefore, if the file name changes, it will be saved in the same directory as the original file.

Now, I would think that this would be the case automatically that under excel when you save a file that was a result of making changes to the original file, that it would be saved in the same directory. However, I have had situations where the file would be saved under my documents instead of the desired subdirectory of my documents.

Do you know why that is the case?

Thank you.
 
Upvote 0
Hey there,

I'm SO sorry it has taken me this long to get back here (darn that whole 'real life' thing).

So--what point are we at? Things are a little jumbled up here and I've kind of lost the thread of what's happening. Assuming you still need (want) the help, care to post a little summary? I'm not sure what you may have done in the meantime so we may as well try to figure out where things are right now.
 
Upvote 0
No worries. I will try to summarize as follows:

1. Case sensitive for file name extension

Is that is it possible to write a code that will remove xls if added by the user in any case format (i.e. upper or lower case). That is if the user were to name a file Test.XLS or Test.Xls or Test.XLs,....(all the permutations/combinations of upper and lower cases of xls), that the xls would be removed. Right now, only "xls" is removed but not any combinations of upper or lower cases of "xls" is not removed.


2. File directory

I want the file directory to be the same as the file that it is saved on. For example, if the file is saved under a subdirectory "Userform" of my documents, I want it saved under that "Userform" subdirectory.

However, sometimes I save it and try to find the saved file under the subdirectory "Userform" and I can't find it - only to find it under my documents. In other words, it was saved but not under the correct subdirectory. Why?

3. Modifications

Part 1:

I modified the excellent code that you had provided to incorporate the option of allowing the user to save the same filename - thereby, eliminating the repetitive task for the user of inputting the same filename.
The changes are highlighted in red.
Your thoughts on the approach listed below would be greatly appreciated.

Rich (BB code):
Sub Save_Click()
Dim Ans As String
'show inputbox for filename - user can click cancel, keep existing filename or _
    create new file name
   Ans = MsgBox("Do you want to save the file under the same file name?", _
  vbYesNoCancel + vbQuestion)
Select Case Ans
  Case vbYes
            ActiveWorkbook.Save
                If MsgBox("Do you want to exit the file?", vbYesNo + vbQuestion) _
                    = vbYes Then ActiveWorkbook.Close
                    Exit Sub
         Case vbNo
      DifferentName
   Case vbCancel
      Exit Sub
     End Select
End Sub
 
 
Sub DifferentName()
Dim fName As String, fPath As String
Dim Chk As Variant
fPath = ActiveWorkbook.Path
'inputbox for filename until user clicks cancel or enters a filename
Name_1: Do
    fName = Application.InputBox("Please enter file name", Type:=2)
    If fName = "" Then MsgBox "You did not enter a filename", vbCritical
    If fName = "False" Then Exit Sub 'if clicks cancel, end macro
Loop While fName = ""
 
'remove instance of '.xls' if it was added by user
fName = Replace(fName, ".xls", "")
'check if file already exists
If FileExists(fPath & Application.PathSeparator & fName & ".xls") Then
    'if file exists, show dialog and capture results
    Chk = MsgBox("A file named '" & fName & ".xls' already exists in this location." _
    & vbCrLf & "Do you want to replace it?", vbYesNoCancel + vbInformation)
    If Chk = vbCancel Then Exit Sub 'if cancel clicked, end macro w/out saving
    If Chk = vbNo Then GoTo Name_1 'if no clicked, go back to loop and allow user to enter new filename
End If
'if yes, code continues here and saves file
'save workbook with .xls extension
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=fName & ".xls"
Application.DisplayAlerts = True
If MsgBox("Do you want to exit the file?", _
vbYesNo + vbQuestion) = vbYes Then ActiveWorkbook.Close
End Sub

Part 2:
I copied the code for closing and exiting the file above for the exit button:

Rich (BB code):
Sub eXIT_Click()
' To close the file
If MsgBox("Do you want to exit the file?", _
vbYesNo + vbQuestion) = vbYes Then ActiveWorkbook.Close
End Sub

It seems to work fine - the only thing is that the cursor is in its hour glass mode but when you hover it over the message box the hour glass disappears and the cursor reappears so that you can click on yes or no buttons. My concern is that the user will think the program is in its thinking mode when the cursor is being displayed as a hour glass and will not hover his/her mouse over the message box to make the selection. Any reason as to why it is in its hour glass state?

4. Simple (but silly question)

Last question - I defined the exit button when I clicked on assign macro and it adjusted it as eXIT. Reason?


If the any of the above is not clear, please let me know.

Thank you.
 
Upvote 0
1. Case sensitive for file name extension

Is that is it possible to write a code that will remove xls if

added by the user in any case format (i.e. upper or lower case).

That is if the user were to name a file Test.XLS or Test.Xls or

Test.XLs,....(all the permutations/combinations of upper and lower

cases of xls), that the xls would be removed. Right now, only "xls"

is removed but not any combinations of upper or lower cases of

"xls" is not removed.

Ah, I forgot that Replace is case-sensitive. Easy enough fix,

though.

Change this
Code:
fName = Replace(fName, ".xls", "")

to this
Code:
fName = Replace(fName, ".xls", "", Compare:=vbTextCompare)



2. File directory

I want the file directory to be the same as the file that it is

saved on. For example, if the file is saved under a subdirectory

"Userform" of my documents, I want it saved under that "Userform"

subdirectory.

However, sometimes I save it and try to find the saved file under

the subdirectory "Userform" and I can't find it - only to find it

under my documents. In other words, it was saved but not under the

correct subdirectory. Why?

Looks like we forgot to tell it to use the path it's grabbing at

the beginning of the code (oops).

Change this
Code:
ActiveWorkbook.SaveAs FileName:=fName & ".xls"

to this
Code:
ActiveWorkbook.SaveAs FileName:=fPath & 

Application.PathSeparator & fName & ".xls"


3. Modifications

Part 1:

I modified the excellent code that you had provided to incorporate

the option of allowing the user to save the same filename -

thereby, eliminating the repetitive task for the user of inputting

the same filename.
The changes are highlighted in red.
Your thoughts on the approach listed below would be greatly

appreciated.
Looks fine to me! :)

Part 2:
I copied the code for closing and exiting the file above for the

exit button:

It seems to work fine - the only thing is that the cursor is

in its hour glass mode but when you hover it over the message box

the hour glass disappears and the cursor reappears so that you can

click on yes or no buttons. My concern is that the user will think

the program is in its thinking mode when the cursor is being

displayed as a hour glass and will not hover his/her mouse over the

message box to make the selection. Any reason as to why it is in

its hour glass state?

I really don't know why it would be showing the hourglass. You could try editing the msgbox text to let them know they can still click the options in the box?

4. Simple (but silly question)

Last question - I defined the exit button when I clicked on assign

macro and it adjusted it as eXIT. Reason?

No idea on that one either, I'm afraid. I was able to rename the sub EXIT_Click with no apparent issues.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top