I have copied the code for this button.

The purpose of the button is to save the excel sheet at a specific location,
and to save it. I want a prompt box to appear, which asks what name the user
wants to save it as. Usually this name will be the date.

I am getting this message:
Message: Run time error: '424'
Debug = MyFileName = "MyName" & UserForm1.TextBox1 & ".xls" 'Create the
File Name
----------------------------------------------------------------------------
---------------------------------------------------------

Private Sub CommandButton1_Click()
MyPath = "s:shared\Enforcement, Enquiry & Complaints" 'Set
the Path
MyFileName = "MyName" & UserForm1.TextBox1 & ".xls" 'Create the File Name
MySaveString = MyPath & MyFileName 'String the two together

ActiveWorkbook.SaveAs Filename:= _
MySaveString, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1 = Date
End Sub
Private Sub TextBox1_Change()

End Sub

----------------------------------------------------------------------------
-------------------------------------------------------------

Please help

Hi Ravi,
Textbox1 is an object.
Change MyFileName = "MyName" & UserForm1.TextBox1 & ".xls" 'Create the
File Name
to: MyFileName = "MyName" & UserForm1.TextBox1.text & ".xls" 'Create the
File Name


--
John
johnf 202 at hotmail dot com


"Ravi Sandhu" wrote in message
...
| I have copied the code for this button.
|
| The purpose of the button is to save the excel sheet at a specific
location,
| and to save it. I want a prompt box to appear, which asks what name the
user
| wants to save it as. Usually this name will be the date.
|
| I am getting this message:
| Message: Run time error: '424'
| Debug = MyFileName = "MyName" & UserForm1.TextBox1 & ".xls" 'Create the
| File Name
| --------------------------------------------------------------------------
--
| ---------------------------------------------------------
|
| Private Sub CommandButton1_Click()
| MyPath = "s:shared\Enforcement, Enquiry & Complaints" 'Set
| the Path
| MyFileName = "MyName" & UserForm1.TextBox1 & ".xls" 'Create the File
Name
| MySaveString = MyPath & MyFileName 'String the two together
|
| ActiveWorkbook.SaveAs Filename:= _
| MySaveString, FileFormat:= _
| xlNormal, Password:="", WriteResPassword:="", _
| ReadOnlyRecommended:=False, CreateBackup:=False
| End Sub
|
| Private Sub UserForm_Initialize()
| Me.TextBox1 = Date
| End Sub
| Private Sub TextBox1_Change()
|
| End Sub
|
| --------------------------------------------------------------------------
--
| -------------------------------------------------------------
|
| Please help
|
|
|
|

Hi Ravi
you're trying to use the object reference in the string concatenation.
Try
MyFileName = "MyName" & UserForm1.TextBox1.text & ".xls"

HTH
Frank

Ravi Sandhu wrote:
I have copied the code for this button.

The purpose of the button is to save the excel sheet at a specific
location, and to save it. I want a prompt box to appear, which asks
what name the user wants to save it as. Usually this name will be the
date.

I am getting this message:
Message: Run time error: '424'
Debug = MyFileName = "MyName" & UserForm1.TextBox1 & ".xls"
'Create the File Name
---------------------------------------------------------------------
-------
---------------------------------------------------------

Private Sub CommandButton1_Click()
MyPath = "s:shared\Enforcement, Enquiry & Complaints"
'Set the Path
MyFileName = "MyName" & UserForm1.TextBox1 & ".xls" 'Create the
File Name MySaveString = MyPath & MyFileName 'String the two
together

ActiveWorkbook.SaveAs Filename:= _
MySaveString, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1 = Date
End Sub
Private Sub TextBox1_Change()

End Sub

---------------------------------------------------------------------
-------
-------------------------------------------------------------

Please help