auto save when form is closed

[tpeter]

I have a userform that pops up when a file is open to make sure all of the
data goes into the correct place. I would like to add 1 more textbox that
would give the used the ability to type in the file name, an example would be
4-4-08 Run 1. When the form is closed I would like to have the file
automatically saved to a certian location with the file name added. An
example would be I/work/engineeringdata/productdata/meters/turbine/(file name
entered by user).xls. I could make a macro to run on the forms close to save
the file name but am unsure how to attache the userinfo to the path and name.
Thank you for your help.

Tim Peter

 

[Jeff]

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
ThisWorkbook.Save
End Sub

 

[Jeff]

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.Dialogs(xlDialogSaveAs).Show ("4-4-08 Run 1")
End Sub

 

[tpeter]

Jeff,

That worked great thank you. I subsituted ("4-4-08 Run 1") for ("textbox26")
and it did exaclty what I wanted. I still need to be able to put the path
into this location.I:\work\Engineering Data\Product Data\Meters\Turbine
Meters, I have tried putting it into the () and outside and I get an error.
Any idea's? Thank you again for your help.

 

[Jeff]

Try this

Option Explicit
Private Const fPath As String = "I:\work\Engineering Data\Product
Data\Meters\Turbine Meters\"
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
ThisWorkbook.SaveCopyAs (fPath & Chr(32) & TextBox26)
End Sub

 

[tpeter]

Jeff,

I tried this and can't tell if it is working or not. fPath is breaking the
code. I have made sure the path is correct for my network and gotten rid of
the : between I and Work but it still breaks. Any Idea's? Thanks again for
your assistance.

 

[Jeff]

this should do it....


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim fPath As String ' Variable to hold filepath
Dim fExtention As String 'Variable to hold File Extention
Dim txtBoxValue As String 'Variable to hold TextBox Value
Dim SavePath As String 'Variable to define final file path

'in your case this should be _
I:\work\Engineering Data\Product Data\Meters\Turbine Meters\
fPath = "C:\Documents and Settings\User\Desktop\"

' I am assuming you want to save this as an ".xls"
'but it can be whatever you need.
fExtention = ".xls"

'This Just stores the TextBox26 value
txtBoxValue = UserForm1.TextBox26.Value

'Packages it all up to a nice neat variable.
SavePath = fPath & txtBoxValue & fExtention

'Saves a copy of the active workbook to the
'specified location.
ThisWorkbook.SaveCopyAs (SavePath)
End Sub