Solved: File Save As

[antonc]

Hi Guys,

I have done a search, but can't seem to find anything that helps me.

I've attached a file for ease of explanation.

I'm trying to compile a forecast budget master file for my branches.

I'm have a bit of trouble trying to get the file to save as another filename based on the branch that a user selects.

The idea is that when any user opens the master file (I have made this a readonly file), a userform will force them to select a branch that they will work on (along with other options). When the OK button is pressed, I want to automatically save the file as another filename, based on the branch that they select. I want to save this file based on a naming convention that we have at this company.

The code for the ok button is :

ActiveWorkbook.SaveAs FileName:=Range("S2").Value

This doesn't seem to have any effect on the filename.
Is it because S2 refers to a formula?

Please help.

Thanks in advance for you time and effort and any help will be greatly appreciated.

[Bob Phillips]

The problem is that the SaveAsUI argument is only true if the Save as is done from a dialog, the UI part of the name, not if you issue a SaveAs command.

You will need some more logic to handle that situation through you BeforeSave code.

[antonc]

Sorry, Now I'm confused - I'm not a VBA regular by any means and I have no idea what to do now... What type of logic are you refering to?

[Bob Phillips]

Well who wrote the rest of the code that you have then?

[antonc]

That was me, though it is hardly from a level of understanding though - just bits that i have picked up from the net. Copying and pasting and trying to adapt to my needs - that's all.

[Bob Phillips]

See if this works.

Add this code into one of the standard code modules

[vba]

Public SaveName As String
[/vba]

and change the form code OK procedure to

[vba]


Private Sub cmdOK_Click()

frmSelect.Hide
SaveName = Range("S2").Value
ActiveWorkbook.Save

End Sub
[/vba]

and then change the CustomeSave procedure to

[vba]

Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False

'Record active worksheet
Set aWs = ActiveSheet

'Hide all sheets
Call HideAllSheets

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
ElseIf SaveName <> "" Then
ThisWorkbook.SaveAs SaveName
SaveName = ""
Else
ThisWorkbook.Save
End If

'Restore file to where user was
Call ShowAllSheets
aWs.Activate

'Restore screen updates
Application.ScreenUpdating = True
End Sub
[/vba]

[antonc]

Thanks LD - appreciated - will try now...

[georgiboy]

[VBA]Private Sub cmdOK_Click()

frmSelect.Hide
ActiveWorkbook.SaveCopyAs Range("S2").Value & ".xls"

End Sub[/VBA]

[antonc]

XLD - sorry for being a pain, Where should I insert the customsave procedure? Into the form? or one of modules?

[antonc]

Georgiboy,

How do i now activate this workbook directly?

[georgiboy]

How do you mean?

[georgiboy]

Do you mean like this. Once you have saved the copy you can open it, then activate it, then write to it from the master sheet macros:

[VBA]Workbooks.Open (Range("S2") & ".xls")
Windows(Range("S1") & ".xls").Activate
ActiveWorkbook.Sheets("Instructions").Range("A1").Value = "Hello"[/VBA]

Hope this helps