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.
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: