Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workbook Declaration
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workbook Declaration
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 "Varne" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workbook Declaration
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 wrote: 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 "Varne" wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workbook Declaration
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 "Dave Peterson" wrote: 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 wrote: 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 "Varne" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA - variable declaration | Excel Discussion (Misc queries) | |||
type declaration characters | Excel Discussion (Misc queries) | |||
type declaration characters | Excel Discussion (Misc queries) | |||
Macro / Compile Error / Duplicate Declaration | Excel Worksheet Functions | |||
Use DocProps in a change declaration | Excel Worksheet Functions |