Save Command Button Help!

I am trying to create a command button that is attached to a macro that will allow the user to save the worksheet that prompts the user for a specified file name. Or automatically save the worksheet as a field name title. Currently this is the code I have:
Private Sub SaveButton_Click()

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\MYoung\Desktop\h&h VALVE\" & Range("Company:"), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

or
Sub Save()
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\MYoung\Desktop\h&h VALVE\" & Range("Company:"), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

the top is code I just applied to the command button to try it, and the bottom is the code for the macro.

I would appreciate any help I can get on this. Thank you in advance.

You want to determine the filename prior to saving the workbook.
Use the GetSaveAsFilename method.

For example:

Dim defaultName As String
Dim myFileName As String


defaultName = Range("Company")

myFileName = Application _
.GetSaveAsFilename(defaultName, "Excel Workbook (*.xls), *.xls", , _
"Save a new version of the workbook")

If myFileName <> "False" Then
ActiveWorkbook.SaveAs Filename:=myFileName
End If

I would suggest that you drop the default directory and let the user decide.
I do not think that you can set it easily in VBA.

Hope this helps steer you the right way.
--
Les Torchia-Wells


"EnGo" wrote:

>
> I am trying to create a command button that is attached to a macro that
> will allow the user to save the worksheet that prompts the user for a
> specified file name. Or automatically save the worksheet as a field
> name title. Currently this is the code I have:
> Private Sub SaveButton_Click()
>
> ActiveWorkbook.SaveAs Filename:= _
> "C:\Documents and Settings\MYoung\Desktop\h&h VALVE\" &
> Range("Company:"), _
> FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> End Sub
>
> or
> Sub Save()
> ActiveWorkbook.SaveAs Filename:= _
> "C:\Documents and Settings\MYoung\Desktop\h&h VALVE\" &
> Range("Company:"), _
> FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
> ReadOnlyRecommended:=False, CreateBackup:=False
> End Sub
>
> the top is code I just applied to the command button to try it, and the
> bottom is the code for the macro.
>
> I would appreciate any help I can get on this. Thank you in advance.
>
>
> --
> EnGo
> ------------------------------------------------------------------------
> EnGo's Profile: http://www.excelforum.com/member.php...o&userid=36863
> View this thread: http://www.excelforum.com/showthread...hreadid=565763
>
>