Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code Problem in BeforeSave Event | Excel Programming | |||
BeforeSave event | Excel Discussion (Misc queries) | |||
BeforeSave sub won't save another workbook when triggered by another event sub | Excel Programming | |||
BeforeSave event | Excel Programming | |||
BeforeSave workbook event | Excel Programming |