Workbook_BeforeSave - driving me crazy!
 

Hi All,

The situation is this:

I have a workbook being used as psuedo template. When it is opened the
1st thing the user has to do is enter a date via a calendar picker. The
week no forms part of the default file name which is contained in say
cell A1 ie myfileWK30.

For control purposes if the file is saved using either, Save, Save As
or by using the Save icon it must be saved using the filename in cell
A1 ie myfileWK30

My code to try to do this is pasted below and l am failing woefully.
Additionally the SaveAs dialog is showing when it is not suppossed to!

All help very gratefully appreciated

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim DFN
Application.EnableEvents = False
DFN = Sheets("Sheet1").Range("A1").Value & ".xls"
If ThisWorkbook.Name < DFN Then
DFN = Sheets("Sheet1").Range("A1").Value
ThisWorkbook.SaveAs DFN
Else
ThisWorkbook.Save
End If
Cancel = True
Application.EnableEvents = True

End Sub

Regards

Michael Beckinsale

Workbook_BeforeSave - driving me crazy!
 

Doesn't seem wrong, but you can simplify it

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim DFN
Application.EnableEvents = False
Application.DisplayAlerts = False
DFN = Sheets("Sheet1").Range("A1").Value & ".xls"
ThisWorkbook.SaveAs DFN
Cancel = True
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

It MUST go in ThisWorkbook code module


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"michael.beckinsale" wrote in message
oups.com...
Hi All,

The situation is this:

I have a workbook being used as psuedo template. When it is opened the
1st thing the user has to do is enter a date via a calendar picker. The
week no forms part of the default file name which is contained in say
cell A1 ie myfileWK30.

For control purposes if the file is saved using either, Save, Save As
or by using the Save icon it must be saved using the filename in cell
A1 ie myfileWK30

My code to try to do this is pasted below and l am failing woefully.
Additionally the SaveAs dialog is showing when it is not suppossed to!

All help very gratefully appreciated

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim DFN
Application.EnableEvents = False
DFN = Sheets("Sheet1").Range("A1").Value & ".xls"
If ThisWorkbook.Name < DFN Then
DFN = Sheets("Sheet1").Range("A1").Value
ThisWorkbook.SaveAs DFN
Else
ThisWorkbook.Save
End If
Cancel = True
Application.EnableEvents = True

End Sub

Regards

Michael Beckinsale

Workbook_BeforeSave - driving me crazy!
 

Bob,

Thanks for the input. I am glad that you conformed that the code was
OK, l thought l was going mad or just being plain stupid.

Tried both yours & my versions of the code on my laptop and they worked
fine!

Re-booted main desktop, re-tried and all is now OK!

I know its a long shot but have you got any ideas what may have caused
this?????

Regards

Michael Beckinsale

Workbook_BeforeSave - driving me crazy!
 

Sorry Michael, get those problems too often myself, and my view is something
went wrong, its not wrong now, let's move on <G

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"michael.beckinsale" wrote in message
ups.com...

Bob,

Thanks for the input. I am glad that you conformed that the code was
OK, l thought l was going mad or just being plain stupid.

Tried both yours & my versions of the code on my laptop and they worked
fine!

Re-booted main desktop, re-tried and all is now OK!

I know its a long shot but have you got any ideas what may have caused
this?????

Regards

Michael Beckinsale

Workbook_BeforeSave - driving me crazy!
 

Bob,

Cheers, glad to hear the experts have similar problems!

Regards

Michael beckinsale