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 fla
set that allows you to write to the workbook's project. It's not something yo
can change via code. You'll have to explain to each user how to make tha
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 behin
that worksheet. Then just insert that worksheet from that template file with
command like
Dim NewWks as Shee
set newwks = sheets.Add(type:="c:\pathtothatfile.xlt"
or even just include a sheet (hidden) in the same workbook/addin(??) tha
contains the code and copy it from there
But if you want to try writing code that writes code, start by reading Chi
Pearson's site
http://www.cpearson.com/excel/vbe.asp
Here's a version of one of Chip's routines, but for a worksheet event
Option Explici
Sub CreateEventProcedure(
Dim VBProj As Object 'VBIDE.VBProjec
Dim VBComp As Object 'VBIDE.VBComponen
Dim CodeMod As Object 'VBIDE.CodeModul
Dim LineNum As Lon
Dim wks As Workshee
Set wks = Worksheets.Ad
Set VBProj = Nothin
On Error Resume Nex
Set VBProj = ActiveWorkbook.VBProjec
On Error GoTo
If VBProj Is Nothing The
MsgBox "Can't continue--I'm not trusted!
Exit Su
End I
Set VBComp = VBProj.VBComponents(wks.CodeName
Set CodeMod = VBComp.CodeModul
With CodeMo
LineNum = .CreateEventProc("Activate", "Worksheet"
LineNum = LineNum +
.InsertLines LineNum, " MsgBox " & Chr(34) & "Hello World" & Chr(34
End Wit
End Su
===
Heck, maybe you could even use a workbook event instead????????
VBAer wrote:
--
Dave Peterson
Previous Posts In This Thread:
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!
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/tutorial...e2f-d7d870642c44/seo-with-google-msn-and.aspx