Default code in a Worksheet

[vlad4239]

Hi,

I'm wondering if there is a way to have a certain VBA code written by default in a Worksheet. So that if I create new Worksheets, the code will automatically be written in them. Is it possible or is it science fiction?

Thank you,
Vlad4239

 

[AlphaFrog]

Possible.

There is an event procedure called Workbook_NewSheet located in the Thisworkbook module. This is where you will have your code to create new code for the new worksheets. The Workbook_NewSheet event procedure automatically runs when you create a new worksheet.

This is a good tutorial on Event procedures.
http://www.cpearson.com/excel/Events.aspx

This is a good tutorial on how to add vba code at run time.
http://www.cpearson.com/excel/vbe.aspx

 

[AlphaFrog]

What might be a little easier is to copy (then modify) an existing worksheet. If the existing worksheet already has event procedure code, it's copy will have the same code.

 

[RoryA]

If it is event code, you may also want to use the workbook-level equivalent as this fires for all sheets including newly added ones.

 

[MrKowz]

Welcome to the forums!

To expand on what Rorya has suggested, check out the Workbook_SheetChange event. If the code is identical with each worksheet, all you need to do is make sure that you qualify each range with Sh. For example:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Sh
    If Not Intersect(Target, .Range("A1:B10")) Is Nothing Then
        'stuff
    End If
End With
End Sub

Note how I referenced .Range("A1:B10"), not Range("A1:B10")

 

[vlad4239]

Thanks alot! I'll try both of those solution.