Macro To Write Code Onto A Worksheet

  • Hi


    I am using a macro to create worksheets. However, I need to use "worksheet activate" code behind some of these sheets that I have created. Is is possible to use code to actually put code onto a worksheet? The code I need to create is of the following type:


    Code
    Private Sub Worksheet_Activate()
    ...
    ...
    ...
    End Sub



    If anyone knows the answer to this question I'd most interested to hear it!


    Cheers
    Johno

  • Re: Macro To Write Code Onto A Worksheet


    Hi


    The problem I have is that I need a couple of lines of code to be executed every time the new sheet that has been created is seleced by the user. I may have several of these sheets created. I could indeed put the code onto a template sheet, but surely I still need the "worksheet activate" procedure, otherwise how will the newly created sheet know to run the code?


    Johno

  • Re: Macro To Write Code Onto A Worksheet


    Quote from johno359

    Hi


    The problem I have is that I need a couple of lines of code to be executed every time the new sheet that has been created is seleced by the user. I may have several of these sheets created. I could indeed put the code onto a template sheet, but surely I still need the "worksheet activate" procedure, otherwise how will the newly created sheet know to run the code?


    Johno


    If you create a template sheet containing ther code, then use code to copy the template sheet when needed it will have the code in it.

  • Re: Macro To Write Code Onto A Worksheet


    The following line of code creates a simple macro that runs when the workbook is activated, but I need the same code to run when the newly created worksheet is seletced. However, replacing 'ThisWorkbook' with the name of the new worksheet does not work.


    Any ideas why?



    Code
    With ActiveWorkbook.VBProject.VBComponents("Thisworkbooks)").CodeModule
    StartLine = .CreateEventProc("Open", "Workbook") + 1
        .InsertLines StartLine, _
        "Msgbox ""Hello World"",vbOkOnly"


    Johno

  • Re: Macro To Write Code Onto A Worksheet


    Read this, but you can't add code by code if the Project is protected. I still think a hidden template sheet with the code already in it is the best option.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!