Programmatically adding code to ThisWorkbook

Royzer · Oct 11th 2016

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:

Code

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With Target
        .Offset(1).EntireRow.Insert
        .EntireRow.Copy .Offset(1).EntireRow(1)
        With .Offset(1).EntireRow
            .Cells(1).Resize(, 12).ClearContents
            On Error Resume Next
            .SpecialCells(2).ClearContents
            On Error GoTo 0
        End With
    End With


End Sub

Display More

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

Code

Sub SplitWB()
    Dim sht As Worksheet
    Dim newFileName As String
    Const workBookPath = "U:\2016 Excel Projects\01-Split cash sheets into stations project\"
    
   
    For Each sht In ActiveWorkbook.Worksheets
        sht.Copy
        newFileName = workBookPath & sht.Name & ".xlsm"


        ActiveWorkbook.SaveAs Filename:=newFileName, _
            FileFormat:=xlNormal, CreateBackup:=False
         
        ActiveWindow.Close
    Next
End Sub

Display More

Thanks again!

**S O** · Oct 11th 2016

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".

Royzer · Oct 11th 2016

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).

**norie** · Oct 12th 2016

Re: Programmatically adding code to ThisWorkbook

Will the new workbooks contain only one sheet?

Royzer · Oct 12th 2016

Re: Programmatically adding code to ThisWorkbook

Yes, just the sheet copied from the master workbook.

Royzer · Oct 12th 2016

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.

**norie** · Oct 12th 2016

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.

Royzer · Oct 12th 2016

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:

Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 Cancel = True
    With Target
        .Offset(1).EntireRow.Insert
        .EntireRow.Copy .Offset(1).EntireRow(1)
        With .Offset(1).EntireRow
            .Cells(1).Resize(, 12).ClearContents
            On Error Resume Next
            .SpecialCells(2).ClearContents
            On Error GoTo 0
        End With
    End With


End Sub

Display More

jeffhammersley · Oct 13th 2016

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).

**S O** · Oct 13th 2016

Re: Programmatically adding code to ThisWorkbook

Hi Jeff, welcome to Ozgrid

Please can you start a new thread for your question as per the forum rules. Please note for your new thread that it will help us if you can provide the code and maybe an example workbook.

[h]*[/h]

Royzer · Oct 14th 2016

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?

**S O** · Oct 14th 2016

Re: Programmatically adding code to ThisWorkbook

Hi Royzer,

Yes, of course you can still post in this thread - that message is a tad misleading, I'll update it now.

Cheers,

S O

Programmatically adding code to ThisWorkbook

Participate now!

Share