Add event to new worksheet using VBA

(Excel 2003)

I have existing code that adds a new worksheet to the workbook to display data. The worksheet is deleted and recreated whenever a related menu item is selected. I want VBA to insert code into the temporary sheet's CALCALATE event but can't see how to easily do this. (I can keep this simple and just add to the event something like "Call X" (to call module's procedure), that's all I need.

Can anyone help?

Could you not use a hidden worksheet, hidden with the xlSheetVeryHidden attribute, so your users cannot see it in the UI and add this sheet, with the calculate event code already in it, maybe by menu or button. It would be far simpler than trying to write code into the worksheet object. If that is at all possible?

I know it is possible to do it to standard modules but not tried it with a class module

Let us know other ways we might be able to help.

BTW: you are the first genuine poster, so we'd like to get it done!

Hi Anonymous!

This is how I would add a new worksheet and attach the "Calulate" event handler (the example shows a message box each time the calculate event is triggered):

 Private Sub NewSheet_Click()

    Dim oXl As Application: Set oXl = Application
    oXl.EnableEvents = False
    oXl.DisplayAlerts = False
    oXl.ScreenUpdating = False
    oXl.VBE.MainWindow.Visible = False

    Dim oWs As Worksheet
    Dim oVBproj As VBIDE.VBProject
    Dim oVBcomp As VBIDE.VBComponent
    Dim oVBmod As VBIDE.CodeModule '
    Dim lLine As Single
    Const QUOTE As String = """"

    Set oWs = ThisWorkbook.Sheets.Add
    Set oVBproj = ThisWorkbook.VBProject
    Set oVBcomp = oVBproj.VBComponents(oWs.CodeName)
    Set oVBmod = oVBcomp.CodeModule
 
    With oVBmod
        lLine = .CreateEventProc("Calculate", "Worksheet") + 1
        .InsertLines lLine, "    MsgBox " & QUOTE & "Sheet was calculated" & QUOTE
    End With
   
    oXl.EnableEvents = True
    oXl.DisplayAlerts = True

End Sub

For this to work, a couple of things has to be set in Excel:

• In Excel, under Tools->Macro->Security select the tab "Trusted Sources" and check the checkbox that says "Trust access to Visual Basic Project"
• In the VBE (Visual Basic Editor), under Tools->References set a reference to "Microsoft Visual Basic for Applications Extensibility" (VBE6EXT.OLB)

The first is to allow our macro to modify objects in the visual basic project, whilst the second enable us to use the VBProject, VBComponent and CodeModule objects. You are able to to check if access to the Visual Basic Project exits at run time using code something similar to this:

Public Function gotAccessToVBE() As Boolean
    Dim objVBEProj As VBIDE.VBProject
    err.Clear
    On Error Resume Next
    Set objVBEProj = ActiveWorkbook.VBProject
    If err <> 0 Then
        err.Clear
        On Error GoTo 0
        Exit Function
    End If
    On Error GoTo 0
    gotAccessToVBE = True
End Function

For further reading I suggest "Programming the VBA Editor" over at Pearson's.

Good luck with this  

Nick:

Are there any tags I can use to enclose vb code in this forum? I would like to se the code propperly highlighted like in the VBE..

Peder

I'll check it out, thanks for dropping by. It's fairly new as you know ;-)

I knew how to add code in standard modules, but have never added it to classs ones, so thanks for the enlightenment too

Nick Hodge:

Peder

I'll check it out, thanks for dropping by. It's fairly new as you know ;-)

I knew how to add code in standard modules, but have never added it to class ones, so thanks for the enlightenment too

Great!

Yeah, I can see it's new, I picked it up via Daily Dose of Excel this morning. Great initiative, I love people stepping up to inform/help users with Excel. You MVP's are truly great 

Regarding adding code to class modules; I didn't know this myself, just tried it to see if it worked and it did [;)

I should add that the code above is widely inspired by Pearson's and other resources found via Google (I claim no rights )

A propos of not much really, the code posted looks weird in Firefox (2.0.0.11 on my work PC at least) but is legible if I switch to IE.

 I see something like this (if it comes out OK):

I dunno which of "font-family: terminal, monaco" got picked up, but Firefox didn't seem to like it.

A quick Googling turned up an old post of ***'s: http://www.dicks-blog.com/archives/2004/05/14/vba-to-html/ that offers a couple of possible options, and there are a few downloads at Colo's: http://puremis.net/excel/downloads.shtml - I must take a look at the Excel-to-HTML table generator, mine's never really been the full ticket.

mikewoodhouse:

A propos of not much really, the code posted looks weird in Firefox (2.0.0.11 on my work PC at least) but is legible if I switch to IE.

In FF hold down CTRL and use the scrollwheel on your mouse to adjust the font size (alternativly you can hold CTRL and tap plus).

It's actually the font size that makes it garbled (wrong font size on terminal fonts makes it unreadable in FF, at least thats my experience)

Duh. It works at 130% but I only went as far as 120 before. A rare win for IE.

If you have multiple user and don't want to mess with the security on every one of their computers, you could try using the "SheetCalculate" event under the "ThisWorkbook" object. Just test for the name of the sheet being calculated:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Sh.Name = "MySheet" Then

        MyMacro

    End If
End Sub

 HTH, Charles Chickering

One other method I just thought of, you could use a class module similar to what JWalk does for handling events for multiple controls:

In the class module named "WS_Events" add this code:

Option Explicit
Public WithEvents MyWS As Worksheet

Private Sub MyWS_Calculate()
    MyMacro
End Sub

In a separate normal module add this code:

Option Explicit
Public MyWS_Event As New WS_Events

Sub AddSheet()
Dim WS As Worksheet
Set WS = ActiveWorkbook.Worksheets.Add
Set MyWS_Event.MyWS = WS
End Sub

Sub MyMacro()
    MsgBox "Hello World"
End Sub

The "AddSheet" macro would be the macro you have that is adding the sheet. Let me know if you have any questions

 Charles

Thanks for the post.

I would like to make one remark and to ask you one question.

Remark:

the new created sheet with

Set timelineWs = ActiveWorkbook.Worksheets.Add

doesn't have yet a CodeName. You have to call this

        Dim vbp As VBProject
        Set vbp = ThisWorkbook.VBProject
        Set vbp = Nothing

Only now you get the CodeName.

Question:

When I call the function

StartLine = VBCodeMod.CreateEventProc("SelectionChange", "Worksheet") + 1

The VB editor (Microsoft Visual Basic - Timeline.xls - [Sheet8 (Code)]) pop up on the screen. How you disable this anoying window (from the user point of view this window doesn't make any sense)

Thanks in advance,

Razvan

razvan_sirbu:

 

 

Question:

The VB editor (Microsoft Visual Basic - Timeline.xls - [Sheet8 (Code)]) pop up on the screen. How you disable this anoying window (from the user point of view this window doesn't make any sense)

Hey Razvan,

this was an old post to dig up :P

You would probably want to do...

Application.VBE.MainWindow.Visible = False

...right after you have verified access to the VBE. This would hide the VBE so it would not pop up..

razvan_sirbu:

Remark :

the new created sheet with

Set timelineWs = ActiveWorkbook.Worksheets.Add

doesn't have yet a CodeName. You have to call this

        Dim vbp As VBProject
        Set vbp = ThisWorkbook.VBProject
        Set vbp = Nothing

Only now you get the CodeName.

This remark I do not understand.. In my code I do...

Set oVBproj = ThisWorkbook.VBProject

...before...

Set oVBcomp = oVBproj.VBComponents(oWs.CodeName)

...this results in my code running flawlessly. Would you care to elaborate on what you are trying to explain here?