Method proceeds but not taking action-Gurus help here

[Guest]

The following codes include a module and a class. When I run the module, the
Wb_beforesave event is activated and executed. However, the line with
"Sheets.add" is proceeded but a new sheet is not added as expected. Any guru
knows why this happens and how to solve the problem? For certain reason, I
can not move the "sheets.add" line to the test module before
"activeworkbook.save". I just want to know why my codes do not work in the
way it should work. I have posted in severl excel forums but seems no one
could answer. Thanks!

David

Module

Dim X As New Class1
Sub test()
Set X.App = Application
Set X.Wb = ActiveWorkbook
ActiveWorkbook.Save
End Sub

Class Name: Class1
Public WithEvents App As Application
Public WithEvents Wb As Workbook

Private Sub Wb_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

ActiveWorkbook.Sheets.Add after:=Worksheets(ActiveWorkbook.Worksheets.Count)

End Sub

 

[Peter T]

Hi David,

I replicate your problem, indeed it is odd.

It seems not possible to add a sheet in the BeforeSave event that's trapped
with 'WithEvents' at Application level or Workbook level. No problem to make
other changes, eg to cells.

The problem only seems to manifest if the workbook is saved with code,
irrespective of which wb the save-code is in vs. the event code or wb being
saved.

Strangely the event seems to work fine if saved from the UI, which prompts
the following workaround -

Dim cbb As CommandBarButton
Set cbb = Application.CommandBars.FindControl(ID:=3) ' the save button
cbb.Execute

This of course saves the Activeworkbook, your code is only trapping a wb
that was active at specific point time, so you ought really activate the
original activeworkbook first, using your variables -
X.Wb.Activate.

Small point of detail, not that it will impact directly as your object 'Wb'
and the ActiveWorkbook will be the same, but suggest change -

ActiveWorkbook.Sheets.Add

after:=Worksheets(ActiveWorkbook.Worksheets.Count)
Wb.Sheets.Add after:=Wb.Worksheets(Wb.Worksheets.Count)

You have withevents App and Wb, I assume this is just for testing, normally
not necessary to have both. If need to trap app-level and a specific
workbook, set a non- withevents ref to the wb in the class at module level
and compare in app events

If modLevelWbRef Is AppEventWbRef then etc

Back to the workaround, all seems to work fine if the wb is saved either by
user from the UI or with the workaround in 'your' wb. If also need to cater
for code in some other unknown project trying to save the wb (perhaps the
activeworkbook itself) would need to do some more work.

Of course the best solution is to get to the route of the actual problem,
must admit at the moment I don't see how.

Regards,
Peter T