Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving "ThisWorkbook" code
I save all the code I write to reuse or use as a reference later. I
just save the module in a "bas" directory. Now I've written a couple of sets of code in "ThisWorkbook". If I try to save the new code under that file name, it will overwrite the old code. The easiest fix I can think of is to copy the "ThisWorkbook" code to a module, put in a remark that it needs to be in "ThisWorkbook" to run (event triggered) and save the module under some appropriate name. Is this how others do it? Is there a better way? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving "ThisWorkbook" code
Honestly I don't have any reusable code that I would put in thisworkbook. I
assume that the issue is that you want to have access to the Thisworkbook events? if so then you could create a class object to access the events of this workbook something like this... In a Class module called clsEvents Option Explicit Public WithEvents wbkThisWorkbook As Workbook Private Sub Class_Initialize() Set wbkThisWorkbook = ThisWorkbook End Sub Private Sub wbkThisWorkbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) MsgBox Sh.Name End Sub In ThisWorkbook create an instance of clsEvents like this... Option Explicit Dim wbkEvents As clsEvents Private Sub Workbook_Open() Set wbkEvents = New clsEvents End Sub Now you can just store the Class Module and add it into a project as needed... That being said it might be just as easy to copy and paste the code as you have suggested. -- HTH... Jim Thomlinson "davegb" wrote: I save all the code I write to reuse or use as a reference later. I just save the module in a "bas" directory. Now I've written a couple of sets of code in "ThisWorkbook". If I try to save the new code under that file name, it will overwrite the old code. The easiest fix I can think of is to copy the "ThisWorkbook" code to a module, put in a remark that it needs to be in "ThisWorkbook" to run (event triggered) and save the module under some appropriate name. Is this how others do it? Is there a better way? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving "ThisWorkbook" code
On Feb 2, 4:38 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: Honestly I don't have any reusable code that I would put in thisworkbook. I assume that the issue is that you want to have access to the Thisworkbook events? if so then you could create a class object to access the events of this workbook something like this... In a Class module called clsEvents Option Explicit Public WithEvents wbkThisWorkbook As Workbook Private Sub Class_Initialize() Set wbkThisWorkbook = ThisWorkbook End Sub Private Sub wbkThisWorkbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) MsgBox Sh.Name End Sub In ThisWorkbook create an instance of clsEvents like this... Option Explicit Dim wbkEvents As clsEvents Private Sub Workbook_Open() Set wbkEvents = New clsEvents End Sub Now you can just store the Class Module and add it into a project as needed... That being said it might be just as easy to copy and paste the code as you have suggested. -- HTH... Jim Thomlinson "davegb" wrote: I save all the code I write to reuse or use as a reference later. I just save the module in a "bas" directory. Now I've written a couple of sets of code in "ThisWorkbook". If I try to save the new code under that file name, it will overwrite the old code. The easiest fix I can think of is to copy the "ThisWorkbook" code to a module, put in a remark that it needs to be in "ThisWorkbook" to run (event triggered) and save the module under some appropriate name. Is this how others do it? Is there a better way?- Hide quoted text - - Show quoted text - Thanks, Jim. I just keep the code so I can remember how I did it before so I can do something similar when I need to. I keep all my old code in a program where it's easily searchable on any word or fragment in the code so I can find it very quickly and easily. So I could type in "Sheet_Change" or ".xlDown" and find all instances of previously written code with the keyword in an instant. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programatically adding macro to Excel - "ThisWorkbook" | Excel Discussion (Misc queries) | |||
Can I disable a macro in "Thisworkbook" with true or false? | Excel Programming | |||
What type of code do you put in "ThisWorkBook"??? | Excel Programming | |||
How to use VBA to copy this code to "thisworkbook" objects of all opened workbooks ? | Excel Programming |