Track changes in a new generated Worksheet

[Rosser14]

I am trying to figure out how to track changes in a new workbook that is generated from VBA. The document generates a new document and I do not want to have to add the following logic in each new workbook. Is there a way to place it in the VBA Code that generates the document so that if a change is made in the new document, it highlights the change.

'UpdatebyExtendoffice20180307
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Value <> "" Then
Target.Interior.ColorIndex = 6
End If
End Sub

 

[Logit]

.
Think in terms of SAVING the existing workbook (new name) ... minus any unwanted data, sheets, formulas, etc. This way the tracking macro
will be saved along with the workbook.

 

[Norie]

Create the new document from a template which already has that code in it.

 

[Rosser14]

I am still getting use to VBA so forgive me for not following. I want to be able to track changes in the following

wb.Worksheets("Sheet1").Range("A1:H517")

Once this sheet is populated with the info, I want to track any changes made to this group of cells. I just do not want to have the new Workbook populate and then inject the VBA code in "ThisWorkbook". I want to do this in VBA before hand so that when I save the new workbook, this command is there each time.

 

[Norie]

How are you creating the new workbook?

 

[Rosser14]

The original document has various command buttons and user forms that allow the user to select certain variables to populate a list. So for example, they need certain tools for a job but are given all of the tools available. They make the selections and then once complete, it generates a tool list from their selections into a new workbook. I can go into VBA and place the highlight logic in "ThisWorkbook" after they have created it but I do not want to have to go to each user and place this code in each time. I want changes to be highlighted with each new workbook they create.

 

[Norie]

How are you creating the new workbook?

Are you using Workbooks.Add?

If you are then what you can do is use the Template argument to create a new workbook based on an existing workbook rather than creating a completely blank workbook.

That existing workbook would also be 'blank', i.e. no data, formatting etc, but it would have the code you want to include in the new workbook.

For example, let's say you have a workbook named Template.xslm which has the code you want in it.

To create the new workbook you could use this.

Set wbNew = Workbooks.Add(Template:="Template.xslm")

 

[Rosser14]

Thank you for the input. My apologies if I wasn't providing enough clarity. I was able to make your suggestion work.

 

[Norie]

No problem.

I think the idea of using a 'template' is often overlooked when using Excel.