Whitty
08-07-2007, 02:07 PM
I have created a user form in Excel. I added a button that will invoke the "Save As" dialog. I want to change the default file name to the contents of a particular cell plus the word "Original". Can this be done? I know it can be done without going through the Save As window, but we ned to change the location where the file will be saved each time so that is why I need the dialog box.
Here is the code I am using to invoke the Save As function:
Private Sub CommandButton1_Click()
Dim file_name As Variant
' Get the file name.
file_name = Application.GetSaveAsFilename( _
FileFilter:="Excel Files,*.xls,All Files,*.*", _
Title:="Save As File Name")
' See if the user canceled.
If file_name = False Then Exit Sub
' Save the file with the new name.
If LCase$(Right$(file_name, 4)) <> ".xls" Then
file_name = file_name & ".xls"
End If
ActiveWorkbook.SaveAs Filename:=file_name
End Sub
Here is the code that will save the file with the file name as I want it but it does not go through the Save As dialog box:
Private Sub CommandButton3_Click()
Dim fName As String
fName = Range("A1").Value & " Original"
' Change directory to suit your PC, including USER NAME
ChDir _
"C:\"
ActiveWorkbook.SaveAs Filename:=fName
End Sub
Somehow the functionality of the second needs to be added to the first. I have been unsuccessful trying to do just that.
Thanks in advance for the help!
Here is the code I am using to invoke the Save As function:
Private Sub CommandButton1_Click()
Dim file_name As Variant
' Get the file name.
file_name = Application.GetSaveAsFilename( _
FileFilter:="Excel Files,*.xls,All Files,*.*", _
Title:="Save As File Name")
' See if the user canceled.
If file_name = False Then Exit Sub
' Save the file with the new name.
If LCase$(Right$(file_name, 4)) <> ".xls" Then
file_name = file_name & ".xls"
End If
ActiveWorkbook.SaveAs Filename:=file_name
End Sub
Here is the code that will save the file with the file name as I want it but it does not go through the Save As dialog box:
Private Sub CommandButton3_Click()
Dim fName As String
fName = Range("A1").Value & " Original"
' Change directory to suit your PC, including USER NAME
ChDir _
"C:\"
ActiveWorkbook.SaveAs Filename:=fName
End Sub
Somehow the functionality of the second needs to be added to the first. I have been unsuccessful trying to do just that.
Thanks in advance for the help!