In my case I have several check & fill routines in modules, the purpose of which is to extract and format the data I need onto several worksheets. I want those worksheets to automatically run update routines when activated; but I cannot be sure that they will exist in the workbook, so I can't use worksheet event handlers. It is further complicated by the fact that I have several named ranges in the workbook, which users can change, that designate the names of these break-out sheets. Here was my solution, using a Workbook_SheetActivate event handler:

Private Sub Workbook_SheetActivate(ByVal WS As Object)

Dim InfLkpTbl As Range
Dim InfRngName, HRShtName, XRShtName, SRShtName _
As String
Dim InfTblStart(2) As Long
Dim i As Integer

InfRngName = "WshtInf"

'find the appropriate information column for this particular
'resource from a range in the worksheet
Set InfLkpTbl = ThisWorkbook.Names(InfRngName)._
RefersToRange
InfTblStart(1) = InfLkpTbl.Cells(1, 1).Row
InfTblStart(2) = InfLkpTbl.Cells(1, 1).Column


'Set names here from lookup tables in workbook
HRShtName = InfLkpTbl.Cells(InfLkpTbl.Find(what:="Sheet", lookat:=xlWhole, SearchOrder:=xlByColumns).Row - InfTblStart(1) + 1, InfLkpTbl.Find(what:="HR", lookat:=xlWhole, SearchOrder:=xlByRows).Column - InfTblStart(2) + 1).Value
XRShtName = InfLkpTbl.Cells(InfLkpTbl.Find(what:="Sheet", lookat:=xlWhole, SearchOrder:=xlByColumns).Row - InfTblStart(1) + 1, InfLkpTbl.Find(what:="XR", lookat:=xlWhole, SearchOrder:=xlByRows).Column - InfTblStart(2) + 1).Value
SRShtName = InfLkpTbl.Cells(InfLkpTbl.Find(what:="Sheet", lookat:=xlWhole, SearchOrder:=xlByColumns).Row - InfTblStart(1) + 1, InfLkpTbl.Find(what:="Spaces", lookat:=xlWhole, SearchOrder:=xlByRows).Column - InfTblStart(2) + 1).Value

'If worksheet name matches one of the resource sheets,
'fill resources
If WS.Name = HRShtName Then
Call FillRes("HR", False)
Else
If WS.Name = XRShtName Then
Call FillRes("XR", False)
Else
If WS.Name = SRShtName Then
Call FillRes("Spaces", False)
Else
Exit Sub
End If
End If
End If
End Sub



Dave Peterson wrote:

You can do it, but I wouldn't.
12-Mar-09

You can do it, but I wouldn't.

If you're going to share this with others, they'll have to have a security flag
set that allows you to write to the workbook's project. It's not something you
can change via code. You'll have to explain to each user how to make that
change -- and explain it again (and again and again) if they reset that flag.

Instead, I'd either use a separate template file with the code already behind
that worksheet. Then just insert that worksheet from that template file with a
command like:

Dim NewWks as Sheet
set newwks = sheets.Add(type:="c:\pathtothatfile.xlt")

or even just include a sheet (hidden) in the same workbook/addin(??) that
contains the code and copy it from there.

But if you want to try writing code that writes code, start by reading Chip
Pearson's site:
http://www.cpearson.com/excel/vbe.aspx

Here's a version of one of Chip's routines, but for a worksheet event.

Option Explicit
Sub CreateEventProcedure()

Dim VBProj As Object 'VBIDE.VBProject
Dim VBComp As Object 'VBIDE.VBComponent
Dim CodeMod As Object 'VBIDE.CodeModule
Dim LineNum As Long
Dim wks As Worksheet

Set wks = Worksheets.Add
Set VBProj = Nothing
On Error Resume Next
Set VBProj = ActiveWorkbook.VBProject
On Error GoTo 0

If VBProj Is Nothing Then
MsgBox "Can't continue--I'm not trusted!"
Exit Sub
End If

Set VBComp = VBProj.VBComponents(wks.CodeName)
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CreateEventProc("Activate", "Worksheet")
LineNum = LineNum + 1
.InsertLines LineNum, " MsgBox " & Chr(34) & "Hello World" & Chr(34)
End With

End Sub

====
Heck, maybe you could even use a workbook event instead????????


VBAer wrote:

--

Dave Peterson

Previous Posts In This Thread:

On Thursday, March 12, 2009 7:08 PM
VBAe wrote:

Use VBA to create new sheet with event handlers in sheet's code
I tried for a little to do this but was unsuccessful.

Basically, I am using a standard VBA module to build a new worksheet. Using
the same VBA module, I want to write an event handler into the new
worksheet's code. i.e, when I build the new worksheet, I don't want to
separately write the event handler into the worksheet's code.

The basic question boils down to: Can a standard VBA module edit a
worksheet's code?

Is this possible?

Thanks!

On Thursday, March 12, 2009 9:17 PM
Dave Peterson wrote:

You can do it, but I wouldn't.
You can do it, but I wouldn't.

If you're going to share this with others, they'll have to have a security flag
set that allows you to write to the workbook's project. It's not something you
can change via code. You'll have to explain to each user how to make that
change -- and explain it again (and again and again) if they reset that flag.

Instead, I'd either use a separate template file with the code already behind
that worksheet. Then just insert that worksheet from that template file with a
command like:

Dim NewWks as Sheet
set newwks = sheets.Add(type:="c:\pathtothatfile.xlt")

or even just include a sheet (hidden) in the same workbook/addin(??) that
contains the code and copy it from there.

But if you want to try writing code that writes code, start by reading Chip
Pearson's site:
http://www.cpearson.com/excel/vbe.aspx

Here's a version of one of Chip's routines, but for a worksheet event.

Option Explicit
Sub CreateEventProcedure()

Dim VBProj As Object 'VBIDE.VBProject
Dim VBComp As Object 'VBIDE.VBComponent
Dim CodeMod As Object 'VBIDE.CodeModule
Dim LineNum As Long
Dim wks As Worksheet

Set wks = Worksheets.Add
Set VBProj = Nothing
On Error Resume Next
Set VBProj = ActiveWorkbook.VBProject
On Error GoTo 0

If VBProj Is Nothing Then
MsgBox "Can't continue--I'm not trusted!"
Exit Sub
End If

Set VBComp = VBProj.VBComponents(wks.CodeName)
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CreateEventProc("Activate", "Worksheet")
LineNum = LineNum + 1
.InsertLines LineNum, " MsgBox " & Chr(34) & "Hello World" & Chr(34)
End With

End Sub

====
Heck, maybe you could even use a workbook event instead????????


VBAer wrote:

--

Dave Peterson

EggHeadCafe - Software Developer Portal of Choice
SEO With Google, MSN, and Yahoo Site: and Link: counts
http://www.eggheadcafe.com/tutorials...e-msn-and.aspx