Eventcode via macro

Question

I have a lot of Excel sheets (>700, too many to change by hand) and a macro xlm which contains most of the macros for all the sheets. I have functions to loop through all sheets and execute macros in each of those. Because of a request I need to add an Eventhandler via the central macro. Since my understanding of VBA is that a Eventhandler can only be placed in the worksheet code file in the sheet itself I don't now what to do next.

I hope there is a way to do such a thing?

Community · Accepted Answer · 2018-05-08 21:48:18Z

3

Event handler can be in any class module.

Your addin also has sheets, in one of those, you can place:

Private WithEvents xlApp As Excel.Application

Then you will have xlApp in the left dropdown and the events in the right one. Select the ones you want.

Don't forget to set xlApp to something at some point (for example, to ThisWorkbook.Application).

A random reading from Google: Events And Event Procedures In VBA.

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Jan 20, 2012 at 13:18

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheJoeIaut Over a year ago

Does this also work for non application Events? For Example if the selected cells are changed.

Community · Accepted Answer · 2017-05-23 12:15:42Z

If the Event is identical for all sheets then running with the Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) approach suggested by GSerg makes the most sense

You can also add the code to each sheet programmatically - which might be useful if you want to add different "trigger" cells to different sheets based on the sheet names/index.

To add the Data Validation code from your prior question Excel Validation List Increase Font Size you could use this. The code skips normal code modules and the ThisWorkbook module

Sub DumpCode()
    Const vbext_ct_document = 100
    Dim vbProj As Object
    Dim vbComp As Object
    Dim strTxt As String

    strTxt = "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbNewLine _
           & "If Target.Address = ""$A$2"" Then" & vbNewLine _
           & "ActiveWindow.Zoom = 120" & vbNewLine _
           & "Else" & vbNewLine _
           & "ActiveWindow.Zoom = 100" & vbNewLine _
           & "End If" & vbNewLine _
           & "End Sub"

    Set vbProj = ActiveWorkbook.VBProject
    For Each vbComp In vbProj.vbcomponents
        If vbComp.Type = vbext_ct_document Then
            If vbComp.Name <> "ThisWorkbook" Then vbComp.CodeModule.InsertLines vbComp.CodeModule.CountOfLines + 1, strTxt
        End If
    Next
End Sub

Collectives™ on Stack Overflow

Eventcode via macro

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related