Override methods in transient worksheet

nummyofthedowd

New Member
Joined
Apr 10, 2013
Messages
13
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>
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi David and Welcome to the Board,

I'm not sure I understand what you mean by "override the Worksheet_Change method".

If you mean that you want to temporarily disable events so that Worksheet_Change does not get triggered by your code, that can be done like this...

Code:
Application.EnableEvents=False
'....your code that you don't want to trigger events
'....
Application.EnableEvents=True

Please clarify what you meant if I misunderstood.
 

nummyofthedowd

New Member
Joined
Apr 10, 2013
Messages
13
HI Jerry,

Thank you for your response.

I have seen many examples of how to override the Worksheet_Change method, if I open the VBA inteface. I select the workbook and then the worksheet. I can then select the event from the drop down list and add my code (I am working on a custom click event to open a user form).

The problem I have is that my worksheet does not 'exist' to do this. It is created on the fly and the only reference I have is wks (as seen in my code below). I guess in pseudo terms I am trying to do something like

Code:
[COLOR=#574123]Dim xlsInstance As Object[/COLOR]
[COLOR=#574123]Dim wkb As Workbook[/COLOR]
[COLOR=#574123]Dim wks As Worksheet[/COLOR]

[COLOR=#574123]Set xlsInstance = CreateObject("Excel.Application")[/COLOR]

[COLOR=#574123]Set wkb = xlsInstance.Workbooks.Add[/COLOR]
[COLOR=#574123]wkb.Activate[/COLOR]

[COLOR=#574123]Set wks = wbk.Worksheets.Add[/COLOR]
[COLOR=#574123]wks.Activate
[/COLOR]
wks.[COLOR=#574123]Worksheet_Change method = "Sub [/COLOR][COLOR=#574123]Worksheet_Change  *custom code* End Sub"
[/COLOR]

so that when the dynamic sheet is created, so is my custom code.

I am also interested in overriding the Workbook_SheetBeforeDoubleClick method. My problem is not knowing if this is possible but if it is, how to get a handle on this event to customize it.

I hope this is clearer, thanks again for getting back to me.

David
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
If the desired event code does not need to be customized at runtime, consider using a template worksheet, or workbook that has your "override" code in place.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787

ADVERTISEMENT

David, I tapped my previous post from my smartphone, so I was uncharacteristically brief. ;)

You can use VBA to write VBA code; however it's unecessarily complex and riskier than the alternative of using a template worksheet or workbook that already contains the code.

This thread contains an example along with a caution from Mike Rickson regarding "code that writes code".
http://www.mrexcel.com/forum/excel-...rt-variable-value-vbcomponent-codemodule.html
 

nummyofthedowd

New Member
Joined
Apr 10, 2013
Messages
13
If the desired event code does not need to be customized at runtime, consider using a template worksheet, or workbook that has your "override" code in place.

Hi Jerry,

Thanks again for the response, it is appreciated.

I considered using a worksheet and calling it from my app but am concerned about re-use. I am not new to programming but am new to VBA so do not fully understand the end usage restrictions. I am concerned about the limitation on usage if I use a single template. Lets say I have 10 people using my application simultaneously from a network drive, wont that cause issues? I thought that a separate instance of excel and opening transient 'data entry' worksheets would be the way forward.

Maybe I can call transient instances of the worksheet template into the existing project??

Cheers.
Dave
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787

ADVERTISEMENT

Where will the code for the macro(s) that add the worksheets be located? Are you planning on distributing an add-in or placing the code in a user workbook?
 

cytop

Board Regular
Joined
Sep 8, 2010
Messages
84
Other option - use the WithEvents keyword to create an instance of Excel that you can respond to in your own application... Will post details (got to scribble something first), if interested.
 

nummyofthedowd

New Member
Joined
Apr 10, 2013
Messages
13
Thank you both for your responses they are appreciated.

Jerry - the application will be in a master workbook. I am not too sure about add-ins at this stage. I will do some reading, do you recommend one or the other?

cytop - I assume something declared (Dim WithEvents NewXLS As Application) but am not sure how to then say [pseudo] that NewXLS.Worksheet_Change should be " ... ". Again, I will so some reading but some tips would be great.

Cheers,
Dave
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Dave,

The reason I ask about where the code that adds the worksheets is stored, is that workbook would be a logical place to store a template sheet(s) that already have your desired code.
Whether this is a master workbook that users open, or an add-in that is installed on their computers, you know that workbook will exist and be open when the application is running.

If you need to have workbook-level events as well as sheet-level events, then you could use a template workbook at a shared location. Having 10 users simultaneous access the application wouldn't be a problem as you can open the template workbook Read-only and then SaveAs with a specified filename-path.

I'll leave responding to the WithEvents questions to cytop. It's an interesting alternative. I think the key point you could clarify regarding that Dave, is whether the workbook with its added sheets needs to get saved, closed and reopened later without the master workbook. If that's the case, WithEvents seems problematic. Users with an add-in could use a reopened workbook, but if the workbook is opened by someone withouit the add-in installed, the override events would not be present.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top