Override methods in transient worksheet

[nummyofthedowd]

Hi, 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 wkb.Activate Set wks = wbk.Worksheets.Add wks.Activate 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. David

<tbody>
</tbody>

 

[nummyofthedowd]

Hi Jerry,

Thanks for your responses, they have really helped.

I have decide that I will go with worksheet templates and 'harcode' my code. As you say a lot less risk and being new to VBA I would be a fool not to take advice from experienced users such as yourself and mikerickson.

Having used html and JavaScript writing code on the fly is not an issue but I understand VBA is probably not built for it. Apart from avoiding the risks, coding VBA straight into the worksheet is a whole lot easier to implement and test!

Cheers,
Dave