Button Error - Save at location

  • Thread starter Thread starter Ravi Sandhu
  • Start date Start date
R

Ravi Sandhu

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


| 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
 
Back
Top