ActiveSheet_Change is not automatically executing commands

Originally Posted by Norie

Try using the worksheet change event.

To access that right click the tab of the worksheet you want the code to run on and select View Code.

Then from the right dropdown at the top of the code window select Change.

You should now see something like this.

Please Login or Register  to view this content.

This is where you should put any code that you want to be triggered when a change is made on the worksheet.

Target is a reference to the range that has been changed.

To monitor only a specific range on the worksheet you would check what Target is.

If you want the code for all the sheets in the workbook there's the workbook level event SheetChange which goes in the ThisWorkbook module.

Thank you for that. Now the code is actually executed automatically BUT I ran into another problem now. When this is executed then I get the following error Message:

Run-time error '-2147417848 (80010108)':

Method 'Value§ of Object 'Range§ failed

Now the code looks like this:

Please Login or Register  to view this content.

Sure, here it is:

Attachment 261303


Also if you know a simplified code that would make this rule apply for alle the celles A19:A39 without having to Write the same code for each and every cell then I would be happy to recieve som help there aswell

Originally Posted by Norie

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

Se my post above this one. Might I also add that I get this code to work if I try it on other empty cells. The cells in question here from A19 and below are Cells containing Data Validation lists. That means that has to be the issue, but why and how to fix it.....???

Originally Posted by Norie

Enter this in the Immediate Window (CTRL+G).

Please Login or Register  to view this content.

Ok tried that, but nothing changed. I still get the same error I´m afraid

Also I often get the error Message "Not enough system Resources to display completely".

Really weird. I just don´t see why this is happening....

Originally Posted by Norie

The code works for me, though it does call itself over and over again finally ending with the error you mention.

Try this.

Please Login or Register  to view this content.

Waouu that worked Finally. That really feels so good. Thanx Norie

What does this mean, the change you did in the code here? (so that I can learn)


One more question before I Close the thread: How can I simplify my coding so that this rule will apply to all the cells from A19 to A38?

Meaning, how can I code smart so that I do not have to Write (copy/paste and change cell numbers) the code 19 times?

Originally Posted by Norie

The code is an event and is triggered by a change being made on the worksheet.

Since the code itself changes the worksheet it calls itself, over and over.

To stop that happening we need to disable events,

Please Login or Register  to view this content.

while events are disabled we can change the worksheet without the code calling itself.

After we've made the change we enable events to allow the code to continue working.

Please Login or Register  to view this content.

If you want to check an entire range to see if a change is made you can do it like this.

Please Login or Register  to view this content.

Ok, thanx. That makes sense.

This code does do the jobb indeed and saved me a lot of coding. BUt as embarressed I am to say that this caused another problem. If you take my sample workbook and put the last code in and try to trigger it it Works fine. BUT NOW when I hit the "Nullstill Steg 3" Button I get the following error all of the sudden: Run-time error '13': Type mismatch

And the debugger Selects the

Please Login or Register  to view this content.

line and marks it yellow.

Without Your last code this does not happen.

The code for the macro is as follows:

Please Login or Register  to view this content.

Any solution for this?

Again I´m sorry....

Originally Posted by Norie

You could disable events in that code too.

Please Login or Register  to view this content.

Or you could handle it in the existing change event code.

Please Login or Register  to view this content.

Thanx again, so much That solved everything. Learned something to. You´re the best