Workbook Declaration

[Varne]

Hi

Could please some one help me with the following problem?

I found VBA codes to display my message before Excel asking 'yes, no or
cancel' to save before closing. I placed the codes under the Excel object
Workbook (in the module explorer) which has an other 10 or so declarations
which I did not write.

The problem is only those 10 or so codes work. Anything new like the above
one that is put in do not work.

How can I make it work?

I will see my mail only tomorrow.

Thank You.

M Varnendra

 

[Varne]

Hi

I found out the following from VBA help regards to my first post.

I inserted a class module Class1.

Declared App as Application.

Declared X as App in an other module.

Ran the set up codes to connect Class1App to Application from that module.

Then I set up the private sub codes from the class module drop down to write
1 on "a1" before workbook closing event.

It is not working that is when I close the workbook(the event) the class
module codes do not get triggered like worksheet and workbook private sub
codes?

Can somebody help?

I cannot access internet from where I live so I will come back tomorrow.

Thanks
M Varnen

 

[Dave Peterson]

You don't need to use a Class module to do this. You can actually use the
ThisWorkbook module.

Option Explicit
Private WithEvents XLApp As Excel.Application
Private Sub Workbook_Open()
Set XLApp = Excel.Application
End Sub
Private Sub Workbook_Close()
Set XLApp = Nothing
End Sub
Private Sub XLApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Wb.Worksheets(1).Range("a1").Value = 1
'shouldn't you save the workbook, too?
End Sub

This goes in the ThisWorkbook module and makes it a little less confusing (well,
to me anyway).

 

[Varne]

Hi Dave

Thanks. Your codes work. Also the ones I wrote did not work because I did
not write as Excel.Application. That also works now. Thanks again.

Good Day.
M Varnendra