Saving "ThisWorkbook" code

[davegb]

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?

 

[Jim Thomlinson]

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.

 

[davegb]

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






- 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.