SAve file as a cell name

[VbaHell]

Hello one and all

I am using excel 2007.
Is there a way please that when you click on the icon save that the file will be save as two cells using VBA.

IE Cell A1 = John
Cell S2 = 05/09/11
File name would save as A1 & - & S2

John-05/09/11

 

[VoG]

Try like this in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Me.SaveAs Filename:=Sheets("Sheet1").Range("A1").Value & "-" & Format(Sheets("Sheet1").Range("S2").Value, "dd-mm-yyyy") & ".xls"
Application.EnableEvents = True
End Sub

 

[VbaHell]

Hi Vog

This appears not to be working - I have saved the file but it is not renaming as per the VBA Code. If I look at Alt F11 the code is in Module tab under module1 is this correct please

 

[VoG]

No, as I said, it goes in the ThisWorkbook module. In the VBA Project window double click ThgisWorkbook then paste in the code.

 

[VbaHell]

Vog thank you for you help on this, Where do I insert code so that it saves in S:\Branch

 

[VoG]

Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Me.SaveAs Filename:="S:\Branch\" & Sheets("Sheet1").Range("A1").Value & " " & Format(Sheets("Sheet1").Range("S2").Value, "dd-mm-yyyy") & ".xls"
Application.EnableEvents = True
End Sub

 

[VbaHell]

Hi Vog


I have pasted this into the "Thisworkbook" module but it is still not saving correctly.
It keeps saving as the name of the orginal file instead of the cells B13 & G13, I know I am being a nusance on this but if you could spot my mistake that would be great.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Me.SaveAs Filename:="S:\Branch\" & Sheets("Sheet1").Range("B13").Value & " " & Format(Sheets("Sheet1").Range("G13").Value, "dd-mm-yyyy") & ".xls"
Application.EnableEvents = True
End Sub

 

[VoG]

This worked fine for me

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Fname As String
Cancel = True
Application.EnableEvents = False
Fname = "F:\" & Sheets("Sheet1").Range("B13").Value & " " & Format(Sheets("Sheet1").Range("G13").Value, "dd-mm-yyyy") & ".xlsm"
MsgBox Fname
Me.SaveAs Filename:=Fname, FileFormat:=52
Application.EnableEvents = True
End Sub

Maybe events are disabled. Press CTRL + G to open the Immediate Window, enter

Application.EnableEvents = True

and press Enter. Then try again.

 

[VbaHell]

This is now working, Thank you so much for being patient and helping me on this