I defined a class and a module in my excel workbook. They are as the following:
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
#
The problem is if you click "Save", a new sheet will be added. But if you run the module, the sheets.add line is executed but a new sheet is not added. I need the Activeworkbook.save line to activate the save event, instead of clicking the "Save" button from the menu bar. Any guru knows why and how to solve the problem? Thanks!
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
#
The problem is if you click "Save", a new sheet will be added. But if you run the module, the sheets.add line is executed but a new sheet is not added. I need the Activeworkbook.save line to activate the save event, instead of clicking the "Save" button from the menu bar. Any guru knows why and how to solve the problem? Thanks!