Programmatically adding code to ThisWorkbook

  • Hi.


    I have a workbook with 20+ sheets that I am going to split into individual files using VBA. The workbook has some code in ThisWorkbook that inserts rows by double-clicking where you want to insert them. This needs to be added to each workbook that is created when the file is split and saved. I've searched for answers quite a bit and am baffled as to how to make it work. I would really appreciate any help you can give me.
    The code I would like to add to ThisWorkbook in the newly created files is:



    This is the code I am using to split the file:



    Thanks again!

  • Re: Programmatically adding code to ThisWorkbook


    Hi Royzer,


    It's certainly something that can be done, but it requires you to allow programmatic access to the VBProject object model which can lead to a world of security vulnerabilities (which is why it's disabled by default). A simpler way would be to have a template workbook with the code already store and just write the data to that instead of a new workbook and then "Save As".

  • Re: Programmatically adding code to ThisWorkbook


    Quote from S O;779014

    Hi Royzer,


    It's certainly something that can be done, but it requires you to allow programmatic access to the VBProject object model which can lead to a world of security vulnerabilities (which is why it's disabled by default). A simpler way would be to have a template workbook with the code already store and just write the data to that instead of a new workbook and then "Save As".



    Just to make sure that I follow:


    I would do a one-time-only set up of a workbook for each of the sheets I will split out and put the ThisWorkbook code in each of them. Then, each month run a macro to copy the data from each of the sheets in the master workbook and paste it to the corresponding files, right? Because I need to split the sheets out of the master into twenty or so workbooks each month, but the sheets will have the same names each month, as will the receiving workbooks.


    If that is the case, I think the only help I would need then is a way to make sure the data from each sheet in the master workbook goes into the correct existing workbook/template (which would have the same name as the sheet in the master file). For example, columns A:C in the sheet named "KOLN" in the master workbook would paste into the separate workbook named "KOLN". (The data range would always be columns A:C).

  • Re: Programmatically adding code to ThisWorkbook


    I just realized that the copy range I gave you is wrong. Sorry!


    I actually need each worksheet to be copied in its entirety, not just columns A:C.

  • Re: Programmatically adding code to ThisWorkbook


    Have you considered having a blank 'template' workbook that has the same code in it but for the BeforeDoubleClick event rather than SheetBeforeDoubleClick event?


    You could then copy that sheet, copy the required data to the copy and then save the copy with the data, and code.

  • Re: Programmatically adding code to ThisWorkbook


    That is a really good idea. But something I don't understand is happening. I created a new workbook, selected Sheet1 (Sheet1) in Project Explorer, then set the Object drop-down box to Worksheet and the Procedure drop-down to BeforeDoubleClick. Then I dropped in the rest of the code, saved the file, and reopened it.


    The strange thing to me is that it now inserts two rows with a double-click, whereas the SheetBeforeDoubleClick in ThisWorkbook of the master file only inserts one row. It also appears that the copy and paste that the ThisWorkbook version does is not working with the new way.


    Here's the code:


  • Re: Programmatically adding code to ThisWorkbook


    Pardon me for butting in, but this thread looks like a convenient place for the issue I am having myself.
    Basically I have an xlsm file which fetches data from an external source (ADO) and uses copyrecordsettorange (or somesuch) to push it into a worksheet.
    There are actually around 20 different sheets for various data sources.
    Having loaded the data, the code cycles through the rows and depending on particular values add navigation buttons to the worksheet to allow the user to drill down / across / up etc.
    The code also generates on_click events for each button and sticks it in the worksheet's code module.
    This is all working hunky-dory, but then I want to protect my code (especially the main-line) and that is when the fun starts.
    Setting the VBProject 'lock code for viewing' apparently stops programmatic manipulation of the code (not fair, because I am not 'viewing' I am writing code).
    I have trawled half-a-dozen sites looking for ways to programmatically remove the 'VBproject lock' (in the same way as I remove the 'Workbook protection' and reinstate it afterwards every time I un-hide a new worksheet).
    I have seen that the only available option is the 'sendkeys' solution (which is in itself complicated because the key strokes need to be different the first time - existing password required - and the subsequent times).
    But fundamentally I suspect that although I can remove the 'viewing lock', I cannot put it back on at the end of the response.
    Does anyone have any more guidance to give ? (other than don't do it, which is too obvious). If anyone is interested, I can post sections of the code to illustrate what is working (like adding code using code) and not working (like removing VBProject lock).

  • Re: Programmatically adding code to ThisWorkbook


    As the creator of this thread, is it still open for me and anyone who wishes to help me, or do I need to start over in a new thread?

Participate now!

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