CODEWITHSUNDEEP
vbaexcel
cube2016
cube2016

Reputation: 5

Call Worksheet Change events when Excel opens

I have worksheet change events in multiple sheets within an excel workbook.

I would like to call all these change events when I open the excel workbook.

So far, I haven't been able to get it working or figure out a way to do it. I have looked it up but I haven't been able to find anything which shows how to get this done.

Can someone please help me with this?

Upvotes: 0

Views: 1251

Answers (2)

Micah Lindstrom
Micah Lindstrom

Reputation: 365

@Histerical and @newguy definitely have good advice. Check out Workbook and Worksheet events, like listed at https://msdn.microsoft.com/en-us/library/office/dn301171.aspx. You can add the Workbook_Open() event to ThisWorkbook as below. The following code will go through every sheet in the Workbook and run Worksheet_Change if it exists.

Private Sub Workbook_Open()

    Dim s As Worksheet

    On Error Resume Next
    For Each s In Worksheets
        Run s.CodeName & ".Worksheet_Change", Range("A1")
    Next
    On Error GoTo 0

End Sub

The On Error Resume Next and On Error GoTo 0 lines disable the errors that occur when a particular sheet doesn't have a Worksheet_Change event defined. It's probably better practice to remove these lines and define a Worksheet_Change event for every sheet

Upvotes: 1

Histerical
Histerical

Reputation: 304

I agree with @newguy on workbook_open. Try adding a line such as 

Run "Sheet1.worksheet_change", Range("I15")

Change the sheet name to whichever sheets you want to run, but make sure they are how they show up in the VB Editor, not the actual names of your sheets. As well as the range, but you do need to pass along some range for the worksheet_change to function.

Upvotes: 0

Related Questions