Sub won't work on change event

**hutch@edge.net** · 03-09-2010, 03:58 PM

Greetings, Gurus.

I have a small macro that filters and copied data to a "Main" sheet. It works beautifully if I run it from a standard module, but when I try to run it from a Worksheet Change event, it always crashes Excel. Here is the code:

Please Login or Register  to view this content.

Now, I've never run a macro from a worksheet change event, but I read some examples on here, so I right clicked on the "Main" worksheet, and copied my code between these lines:

Please Login or Register  to view this content.

Of course, I removed "Sub Get_Data()" from the top, and "End Sub" from the bottom, but as I said, Excel keeps crashing. What am I doing wrong?

Thanks in advance for any help you can offer.

**shg** · 03-09-2010, 04:19 PM

I don't know how you've actually coded this, but you need to disable events (application.enablevents=false) before doing anything (like pasting data) that will trigger the change event again, and then re-enable events when you're done.

**hutch@edge.net** · 03-09-2010, 05:13 PM

How's it going, Shg? I'm getting the error "Object Method of Range Class Failed"

**shg** · 03-09-2010, 07:16 PM

Post the code your using, and say which line falls over.

**hutch@edge.net** · 03-10-2010, 09:22 AM

I apologize, shg. I was in a hurry yesterday afternoon.

I have pulled all the sensitive data out of the workbook, and attached a sample to this message. I thought this approach might be easier for both of us.

The macro works beautifully if you enter the 3 digit location number, and then click the "Get Data" button. My goal is to have the macro run anytime the user changes the location number in D4, (now on version 3

). I don't currently have the macro stored anywhere except a standard module.

I have all pages locked, with no password, and all but the main page are hidden. The macro unlocks them and unhides them to copy the data, but then locks and hides them again before ending.

Thanks again for all your help!

**shg** · 03-10-2010, 12:44 PM

Hutch,

In the Main sheet module:

Please Login or Register  to view this content.

In the code module:

Please Login or Register  to view this content.

All completely untested ...

**hutch@edge.net** · 03-10-2010, 02:10 PM

I was getting the error message box on all instances, but I replaced the module code with my woking code, and it works great now, (although not as neat as yours). I think my problem was that I was putting the whole code in the worksheet change module, rather than calling it from a standard module, and I didn't have the IF argument in the worksheet module.

Thanks again, shg. This is another one SOLVED!

Have a good one.