Code works in change event but not in module?

**Nitefox** · 03-14-2014, 10:41 PM

Im having problems with the following code. Im planning on using it as part of some userform command button code, but Im separating it out until I can get it working.
I am using the exact same code in my Worksheet Change event code without any issue.
The code should find records from A6 on (to last row in G), and if data exists in G but not in D on the same row, then it should fill it with todays date. If data exists in D but not in G on the same row then D should be cleared.
The code is giving me run time error '424' (Object Required) on this line If Intersect(Target, Range("A6:K"......

Please Login or Register  to view this content.

Thanks,
James

**Nitefox** · 03-15-2014, 12:13 AM

I dont know why it didn't work, but I figured out another way to code it, to effectively give me the same thing:

Please Login or Register  to view this content.

**TMS** · 03-15-2014, 02:21 AM

When you use the With ... End With construct, you need to use a full stop /decimal point / dot to link the items to the objects. So you need a dot in front of each and every reference to Range or Cells.

The second version works more by good luck than anything and might fail or give unexpected results if the ReturnData worksheet is not selected (the active sheet).

Regards, TMS

**Nitefox** · 03-15-2014, 02:26 AM

Thanks TMS, I though since the code specifies With Sheets ("ReturnData") that forces it to work with that sheet only?
Also the userform its working on is only generated by that sheet event procedure.

**TMS** · 03-15-2014, 02:31 AM

Like I said, luck. Read up on With ... End With.

Oh, and the first code fails because the Target range only exists in the Change event handler. When you take the code out of context it has no range object.

Regards, TMS

Code works in change event but not in module?

LinkBack

Thread Tools

Rate This Thread

Display

Code works in change event but not in module?

Re: Code works in change event but not in module?

Re: Code works in change event but not in module?

Re: Code works in change event but not in module?

Re: Code works in change event but not in module?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Textbox Change event handler works once

[SOLVED] Code Gives error if I run it from Module but works fine when run it from This Worksheet

change the (Worksheet_Change) event for a module (Sub).

Run Event Code In General Module

[SOLVED] Where?Worksheet code module or Worksheet_SelectionChange event han

Bookmarks

Bookmarks

Posting Permissions