naming a workbook

[royboy531]

I would like to use a macro with a userform to name a workbook. is there a code for this? I appriciate any help I could get.

 

[BrianB]

A bit too general perhaps. A general answer ...

ActiveWorkBook.Name=TextBox1.Value & ".xls"

 

[LRRICHEY]

A bit too general perhaps. A general answer ...

ActiveWorkBook.Name=TextBox1.Value & ".xls"

Brian
I just tested this and I get the message

Cant assign to a read only property!

What I need to beable to do is have the macro start the save as process and add something to the name automaticly.
for example if a click on the save as button it will get the save as window to open and the file name will be for example "Book1.xls" but what I need it to say is "RaysBook1.xls" or some other phrase.
If i use this
fName = ActiveWorkbook.Name
fName = RIC & "_" & fName
ActiveWorkbook.SaveAs (fName)
The last line ActiveWorkbook.SaveAs will automaticly save the book but the save as applette will not come up.
I would like the applet to come up so I can verify that the name is what I want it to be
Thanks
Ray

 

[Joe Was]

You need to change the Drive and Folder Path in the code below!
Also, change "\AndWhatEver" to what ever you want added to the default Workbook name, Note: The "\" before the "AndWhatEver" must stay!

Public Sub mySaveAs()
'Open the SaveAs dialog.
Dim fileSaveName As Variant, myPath, myFName$

ChDrive "U"
ChDir "U:\Excel\Test"

myPath = CurDir
myFName = myPath & "\AndWhatEver" & "_" & ActiveWorkbook.Name & ".xls"

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.xls), *.xls", _
InitialFileName:=myFName)

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

End Sub


If you have the initial Workbook opened as a Read-Only copy then use this version of the code:


Public Sub mySaveAs()
'Open the SaveAs dialog.
Dim fileSaveName As Variant, myPath, myFName$
Dim fs As Variant, f As Variant

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(fs.GetFileName(ActiveWorkbook.FullName))

'Re-Set ReadOnly! Note: "1" is the Read Only code!
If f.Attributes And 1 Then f.Attributes = f.Attributes - 1

ChDrive "U"
ChDir "U:\Excel\Test"

myPath = CurDir
myFName = myPath & "\AndWhatEver" & "_" & ActiveWorkbook.Name & ".xls"

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.xls), *.xls", _
InitialFileName:=myFName)

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

End Sub

 

[BrianB]

ActiveWorkBook.Name=TextBox1.Value & ".xls"

Oops. Sorry - end of a long day

Should be

ActiveWorkbook.SaveAs Filename:=Textbox1.Value & ".xls"

 

[royboy531]

BrianB, that worked great. I attached the code to the text box. now I am looking to attach a code to a command button in the same user box to close the user box. any ideas???
thanks for your help
royboy531

 

[BrianB]

To close a *Userform*

Me.Hide
Me.Unload

 

[LRRICHEY]

This worked great!!
Thanks

You need to change the Drive and Folder Path in the code below!
Also, change "\AndWhatEver" to what ever you want added to the default Workbook name, Note: The "\" before the "AndWhatEver" must stay!

Public Sub mySaveAs()
'Open the SaveAs dialog.
Dim fileSaveName As Variant, myPath, myFName$

ChDrive "U"
ChDir "U:\Excel\Test"

myPath = CurDir
myFName = myPath & "\AndWhatEver" & "_" & ActiveWorkbook.Name & ".xls"

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.xls), *.xls", _
InitialFileName:=myFName)

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

End Sub


If you have the initial Workbook opened as a Read-Only copy then use this version of the code:


Public Sub mySaveAs()
'Open the SaveAs dialog.
Dim fileSaveName As Variant, myPath, myFName$
Dim fs As Variant, f As Variant

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(fs.GetFileName(ActiveWorkbook.FullName))

'Re-Set ReadOnly! Note: "1" is the Read Only code!
If f.Attributes And 1 Then f.Attributes = f.Attributes - 1

ChDrive "U"
ChDir "U:\Excel\Test"

myPath = CurDir
myFName = myPath & "\AndWhatEver" & "_" & ActiveWorkbook.Name & ".xls"

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.xls), *.xls", _
InitialFileName:=myFName)

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

End Sub