Locate cells that changes

[annica123]

I have this code, that opens a form when something changes in certain columns. It works fine.

But is there any way that I can locate in the form frmOvertimeFlex there after, exactly which cell that has been changed?

Or is that something I should do before opening this form?

Please Login or Register  to view this content.

[alansidman]

Add a line of code as follows

Please Login or Register  to view this content.

[TMS]

You already know which cell has changed ... the Target. So just use its address.

[annica123]

Yes that kinda works, but I would really have a use of Target.Address in the form that I am opening - frmOvertimeFlex. Is there any way for me to send this Target.Address as a parameter to frmOvertimeFlex?

[TMS]

You're welcome. Thanks for the rep.



The easiest way would be to declare a public string variable and set it to Target.Address before showing the form.

[annica123]

Ok, thanks! But I have a lots of modules and forms, where should I declare it so the variable is availible in all of the application? Like a global variable? Because now I'm doing it on the woorksheet level, right before Worksheet_Change method and that is not working?

[TMS]

Like a global variable?

Yes, in a standard module, either on its own or before any subroutines.

Please Login or Register  to view this content.

[annica123]

Yes, that is excatly how Im doing it, but it doesn't work for me:

Please Login or Register  to view this content.

But when Im calling the variable on the form frmOvertimeFlex, its not working. Its just sying the variable its not declared. And the spelling on the variable itself is ok.

[TMS]

Yes, that is excatly how Im doing it, but it doesn't work for me:

But it isn't.


If the worksheet change event handler is in the right place, that is, in the worksheet module, then the declaration is in the wrong place.

It needs to be, no MUST be in a standard module NOT a sheet module. I'm guessing that's why it says it's not found.

[annica123]

Thank you! I had some other issues that interfered with this one, but now it's working!

[TMS]

You're welcome.