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>
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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
Back
Top