Excel crashes when executing CreateEventProc for Worksheet_Change event

[1man]

Hi Everybody ,
Iam trying to assign a Worksheet_Change event programmatically while creating the sheet during runtime.The sheets will be created at runtime based on a value in array . So am trying to create a event for the sheet through macro.I tried with the following code to create a event .But when executed excel Crashes abruptly.

In my program the sheet will be created dynamically in the for Loop as below
For Each Value In Arr '
Worksheets.Add
ActiveSheet.Name = Value
LoadCode (Value)
Next Value

Following is the code which I use,
sheet As String refers to sheetname which comes as parameter from other method

Please Login or Register  to view this content.

Please help me resolve this issue.

Thanks in Advance

[Norie]

Why not use the workbook level event SheetChange?

Please Login or Register  to view this content.

[1man]

Hi Norie,
Can you please suggest me in detail for how to use the workbook level event sheet change . As am a newbie to excel macro , i would like to know how to implement.

Thanks

[Norie]

What are you doing with you worksheet level code?

[1man]

Worksheet level code is needed because I need to get to all the cell address of the edited cells for a particular editable row . Am using the Worksheet event change so that i can get cell address of the edited cell.

The problem occurs when execute the line ,

Please Login or Register  to view this content.

[Norie]

The code you've posted is for creating an event in the ThisWorkbook module, not a worksheet module.

If you want to create a worksheet event the code should look like this.

Please Login or Register  to view this content.

However I'm suggesting you don't use this code to create events in the new worksheet.

Instead you can use the SheetChange event in the ThisWorkbook module.

The SheetChange event is triggered when any sheet in the workbook is changed.

It takes 2 arguments, Sh which is the sheet the change has occured on and Target which is the range on that sheet that has been changed.

[1man]

Thanks for your suggestion and Will try the same .My doubt is how can it be invoked from module.Do i need to call the Workbook_SheetChange explicitly in my module??

[Norie]

No, it's triggered whenever a change is made on any worksheet.

Why not try it with the code I posted in post #2?

[1man]

Hi norie,
Your suggestion works but the scenario is I have a method in macro which assigns the formula to each cell row by row for the whole sheet wherever applicable.After this formula assignation the user is allowed to edit some of the specific rows(Other rows will be protected ) , So I need to find the specific user edited cell value in a string which will then be posted to DB .Can you suggest how I can change this Workbook_SheetChange that fits this scenario.

[Norie]

As I said there are 2 arguments passed to the SheetChange event, Sh and Target.

Sh is the sheet that has been changed.

Target is the range that has been changed, ie the cell the user has edited.

[1man]

Thank you very much for your quick response , Will try the same

[1man]

I have tried the same ,The SheetChange event is good option . But the problem is the event is getting triggered when the macro is in run state . In my scenario as mentioned above it is getting invoked when assigning the formulas to the Cells, But i need to trigger the event after the macro run. i.e When the macro is not in running mode.
Is there any way to check the same.

Infact because of it is getting triggered when macro is run , Am getting Type mismatch error
below is the code for the Event

Please Login or Register  to view this content.

[Izandol]

You may use:

Please Login or Register  to view this content.

to turn off event code while your macro is running, then use:

Please Login or Register  to view this content.

to turn back on when your macro has finished changing the sheets.

[Norie]

You would have the same problems using Worksheet_Change.

If you don't want the change event triggered when the macro is run use Izandol's suggestion.

If you still get the type mismatch try checking what Target is, specifically how many cells it represents.

[1man]

I have used Izandol's Suggestion and it works fine..

Thanks for your help Norie and Izandol..