I am developing a VBA for Excel application which uses transient worksheets to collect data from users before writing the data back to master spreadsheets.
My application opens an instance of excel with a workbook and worksheet using the following code:
Dim xlsInstance As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set xlsInstance = CreateObject("Excel.Application")
Set wkb = xlsInstance.Workbooks.Add
Set wks = wbk.Worksheets.Add
I can reference various ranges in the worksheet and do most of what I need but I need to override the Worksheet_Change method. Easily done for static sheets, but this must be set at runtime when the worksheet is created.
I have made many attempts to override the method and searched forums without success.
Does anyone have experience doing this and can provide some advice?
Thanks in advance.