Worksheet Change Event

Hi all,

With some very useful help from a forum member I was able to create some code which works well in a separate module. However when I copy and paste it into a sheet (worksheet change) module the code doesn't seem to work.

Please Login or Register  to view this content.

Cheers,

Andrew

Andrew,
Unless, I am missing something, you do not seem to have a target column, or row which with the intersction with another range could fired the code.
I think the simplest option (IMO) would be to have your main code on standard module and create another event driven macro,like

Please Login or Register  to view this content.

You can easily change the target and range which are the triggerig points for your macro to run.

Thanks AB,

Yes, I do not seem to understand the target column/row relationship and how to use it in a worksheet change event. What in yr example is range ("F19") referring to?

Andrew,
In your case it is actaully is not "Change event", rather "Selection Event", i.e your macro will be triggered by selecting a cell, or range, but the underlying rules are the same.

In my example, what I am saying is: If I change a value in F19, call my macro, so the code will always fired when ever I change the value of F19. This could easily be a whole column or row. For e,g your could

Target. Column =6
When ever I change the value of any row in column F, my code will be triggered.

Thanks AB,

I tried the following but I get a Compile Error Message: Wrong number of arguments or invalid property assignment. Any ideas?

Please Login or Register  to view this content.

Andrew,
Before we do trial and error, could you please tell me which of your column/row is the triggering one? Which of your column/row you wish to change?

AB,

In the example above any change to row 16 should trigger the event. In reality I want it to be column 12 but the same thing happens if I change the above code from target.Row=16 to Target.Column = 12

Andrew

Andrew,
Okay, we should do the following:

Please put this code in the sheet which have your data(Not module)

Please Login or Register  to view this content.

Please move the above code you have now to standard module. I used the name "Sub Andrewchange()"
You can easily change this name to suite you need.
In my case: If I change any value in column 12(On my sheet1) I will call my macro name "Andrewchange", which resides on my standard module.

Hi Ab,

OK I done this. Now the Worksheet Change Event does not seem to be triggered at all (but at least I don't get an error message!)

Any more thoughts?

It still works fine when run from the standard module.

Andrew,
I assume you have 2 different codes:

One is in your souce worksheet. ie right click on the sheet, copy and paste the above code.
Second. rightly you have your regular code on the standard module
Now change any value in column 12 to fire the code name "Andrewchange" and you should at least get an error message.

Hi AB, Yes two codes one in the data sheet and one in a module. So that you can see better what is happening (or rather not happening) please see attached the excel file.

AB,

Sorry, that may have been a bad idea as the sheet is a copy from another workbook and the code references ranges in other sheets from that workbook.

Here is the full worksheet. The change event is in sheet mFinanceAssumtions. The sub RepayCalc is in Module 1

Oddly, it's now working! Thanks very much for all of your help.

Cheers,

Andrew

Sorry Ab to bother you. Something rather strange happened. When I uploaded the file to this thread I changed its name. Then when I opened the file from this thread the code all worked as it should. I then changed the file name back to the original name and now the code doesn't do anything again. Any ideas on this?

Andrew,
I just got back to my PC. As per the attached the code works. I have changed one value in column L and seems to work, so what is the issue?

The issue is a very strange one. The code works when I upload the file from this thread. But when I make any changes to the file (e.g simply add a formula to another sheet in the file) the change event stops being triggered.

Ya, change event is not fire proof code, it sometimes does not behave like a normal code. Mind you, the change event can ONLY be triggered by change in column L, not by any other means, like formulas.

I have been working with this file some more. It seems to suddenly stop working as I work with the file. However, if I save and close the file and then re-open it the code works again usually. Even better if I shut down the computer and restart.

Andrew,
The main point is you have got somewhere to start with;this is your first event code and as you keep playing around with it, you would perfect it in no time.

I know Ab and your help has been very much appreciated.

Jaslak,

Thanks very much I have amended the code and it now seems to be working fine.

Andrew (Not a Mac user)