Save method and BeforeSave event
 

Greetings,
I have a workbook that, before saving, I want to activate a specific
cell. The following works most of the time:
==========
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Call SelectEntryDate

End Sub
==========
The procedure it calls is:
==========
Sub SelectEntryDate()

On Error Resume Next
ThisWorkbook.Names("entry.date").RefersToRange.Sel ect
On Error GoTo 0

End Sub
==========

The only time that it fails that I haven't been able to fix is when I
use a "Save All" routine in personal.xls. That routine is as
follows:

==========
Sub SaveAllBooks()
Dim WkBk As Workbook

Application.ScreenUpdating = False

For Each WkBk In Application.Workbooks
WkBk.Save
Next WkBk

Application.ScreenUpdating = True

End Sub
==========

The workbook saves, but it doesn't select the range. My questions,
therefore, are; Does the save method somehow bypass the beforesave
event? If so, is there a way to make it work? I've tried removing the
ScreenUpdating lines, but it didn't help.

Excel 2000, XP Pro

Thanks to all for the input.

Mike

Save method and BeforeSave event
 

Hi Mike,

Try amending the SaveAllBooks macro to:

Sub SaveAllBooks()
Dim WkBk As Workbook
Dim WBactive As Workbook

Set WBactive = ActiveWorkbook

Application.ScreenUpdating = False

For Each WkBk In Application.Workbooks
If WkBk.Name = "TEST1.xls" Then '<<===== CHANGE
WkBk.Activate
Application.Run (WkBk.Name & "!SelectEntryDate")
End If

WkBk.Save

Next WkBk

WBactive.Activate

Application.ScreenUpdating = True

End Sub
'==========

Change TEST1.xls to the name of the problematic workbook.

---
Regards,
Norman



wrote in message
oups.com...
Greetings,
I have a workbook that, before saving, I want to activate a specific
cell. The following works most of the time:
==========
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Call SelectEntryDate

End Sub
==========
The procedure it calls is:
==========
Sub SelectEntryDate()

On Error Resume Next
ThisWorkbook.Names("entry.date").RefersToRange.Sel ect
On Error GoTo 0

End Sub
==========

The only time that it fails that I haven't been able to fix is when I
use a "Save All" routine in personal.xls. That routine is as
follows:

==========
Sub SaveAllBooks()
Dim WkBk As Workbook

Application.ScreenUpdating = False

For Each WkBk In Application.Workbooks
WkBk.Save
Next WkBk

Application.ScreenUpdating = True

End Sub
==========

The workbook saves, but it doesn't select the range. My questions,
therefore, are; Does the save method somehow bypass the beforesave
event? If so, is there a way to make it work? I've tried removing the
ScreenUpdating lines, but it didn't help.

Excel 2000, XP Pro

Thanks to all for the input.

Mike