Create Private Sub in a new worksheet

[bayles]

Hi,

Quick question: I have a macro that creates a new worksheet and I was wondering if there was any way in VBA where I can create a Private Sub in that new worksheet automatically when the new sheet is created? It is a change event to call another macro when a certain cell in selected.

Thanks
Ryan

 

[Domenic]

Here's an example that creates a worksheet, and adds a change event procedure...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=darkblue]Dim[/COLOR] Wkb [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] Wks [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] sCode [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] TextLine [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=green]'Build the code for the change event procedure[/COLOR]
    sCode = vbTab & "If Target.Address <> ""$B$2"" Then Exit Sub"
    sCode = sCode & vbNewLine
    sCode = sCode & vbTab & "Call Macro1"
    
    [COLOR=green]'Add a worksheet to the active workbook[/COLOR]
    [COLOR=darkblue]Set[/COLOR] Wkb = ActiveWorkbook
    [COLOR=darkblue]Set[/COLOR] Wks = Wkb.Worksheets.Add
    
    [COLOR=green]'Create the event procedure for the newly added worksheet[/COLOR]
    [COLOR=darkblue]With[/COLOR] Wkb.VBProject.VBComponents(Wks.CodeName).CodeModule
        TextLine = .CreateEventProc("Change", "Worksheet")
        .InsertLines TextLine + 1, sCode
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[Macropod]

Do note that, in order to modify things in the VBE programmatically, you have to give trusted access to the VBA project object model, something that isn't a default and can't be given programmatically due to the security issues it raises.

 

[Domenic]

Thanks Paul, I meant to point it out but somehow it slipped my mind. For the benefit of the OP, access can be given as follows...

File > Options > Trust Center > Trust Center Settings > Macro Settings > Developer Macro Settings > Trust access to the VBA object model

 

[bayles]

Thanks guys. Does that mean every time the file is opened and the user wants to create that sheet they will need to adjust the trust access? I imagine this is not a blanket approval for all files with VBA object model of the same name?

This file will be worked on remotely with many different users and they may download the file to work on everyday. Does that mean they will need to grant access everyday?

 

[Kyle123]

Don't do it like that, use the workbook event handler, it's usually much, much cleaner and more straightforward:

In ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address <> "B$2" Then Exit Sub
    Call Macro1
End Sub

 

[bayles]

Don't do it like that, use the workbook event handler, it's usually much, much cleaner and more straightforward:

In ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address <> "B$2" Then Exit Sub
    Call Macro1
End Sub

Thanks Kyle but the sheet will be deleted and created many times when the user works with the file. Doesn't your suggestion need the sheet to remain in the workbook all the time?

 

[Macropod]

Thanks guys. Does that mean every time the file is opened and the user wants to create that sheet they will need to adjust the trust access?

No. Once it's set, trusted access remains so until manually re-set. However, one wouldn't want to leave it that way in practice, meaning it would have to be toggled on/off every time someone want to add a worksheet.

This file will be worked on remotely with many different users and they may download the file to work on everyday. Does that mean they will need to grant access everyday?

Ultimately, yes - unless they're happy to live with the security risks associated with leaving trusted access 'on' full-time.

 

[Kyle123]

No, it doesn't