Capture Cell Value Before Change Event

[jaslake]

I am trying to capture the value of a cell before a change even. The attached Macro will report the value before the change but does not store it so that I can use it in another module. What I am trying to do is capture the value before the change and then look that value up in another worksheet (in the same workbook) so I can make the same change in the second workbook.

The values will always be in Column B and will always be string characters.

The code I am using for the change event is as follows:

Please Login or Register  to view this content.

Can you give me some direction on how to capture the OldValue so as to use it as a variable in another module?

Thanks...John

[Leith Ross]

Hello jaslake,

Move the dim statement out of the Sub procedure into the General Declarations section of the module (the very top of the module). Your variable when then retain its value for as long as the VBA Project is open and be visible to all modules.

Example

Please Login or Register  to view this content.

[jaslake]

I have added "Public OldValue As String" into the General Declarations as you suggested. This is in worksheet AnnualBudget. My Code now looks like this:

Please Login or Register  to view this content.

So, I change a cell in "B7:B132" in the AnnualBudget worksheet and then run the following Module:

Please Login or Register  to view this content.

and I get this error message:


Microsoft Visual Basic
Compile error:
Ambiguous name detected: OldValue
OK Help


If I add "Public OldValue As String" to this second Module (called ModuleTest) as follows:

Please Login or Register  to view this content.

the module runs but shows OldValue = "".

I would expect OldValue to contain the text of the cell I changed on Annual Budget worksheet (the text before I changed it). I want to find the old text in the Catagories worksheet so I can change it there also.

What am I doing wrong?

Any direction you can give me is appreciated.

John

[Leith Ross]

Hello John,

Can you post a copy of your workbook in Excel 2003 format?

[jaslake]

Sure can Leith. The macros I am concerned about are those behind worksheet6 and the "Moduletest".

As I change items in column B in AnnualBudget, I also need to find the changed item in Categories worksheet and apply the same change. So, as you look at this, keep in mind that I will need to capture "OldValue" and "NewValue". I will need to find "OldValue" in Categories worksheet and replace it with NewValue.

Attached is the Workbook; please ignore the #REF errors, etc. This is a test file.

Thanks for your help and concern. You guys are great (man, woman or child, no matter).

John

[Leith Ross]

Hello John,

I am not sure I follow what you are trying to do. Is it the category name or the value for a given month that you're tracking? Can you give an example of what is changing on the worksheet?

[jaslake]

I'm developing this workbook for my children (and me) to use to track their monthly spending against a budget.The workbook I sent you is missing several worksheets simply because the file was too large to include them.
I am allowing users to change category items on the AnnualBudget
The worksheets you have are the significant items. The major file you are missing is a "Check Register" worksheet. This worksheet is where detail transactions would be entered, very much as an actual check register or as one would enter into Quicken (I actually download and reformat my Quicken entries to feed the Check Register worksheet).

The Categories worksheet you do have is a validation table to validate entries that are made to the Check Register worksheet. Check Register entries are then summarized into a Category Report worksheet which are then fed into the AnnualBudget worksheet (the "Actual") column.

So, it is important that the category items in the "Category" worksheet be the same as the category items in the "Annual Budget" worksheet.

I am allowing users to change category items in the "AnnualBudget" worksheet. So, when they do, I need to make certain the same changes are made in the "Category" worksheet, which is really the validation table.

As I am responding to you, I am thinking of "traps" I have built for my self in the flow of things (Oh Well).

Long and short of it, if a user makes a change in a category on the Annual Budget worksheet, the same change needs to be made in the Category worksheet. I wish to do this pragmatically. If there is a change event in column B of the Annual Budget worksheet, make the same change in the Categories worksheet so that they are kept is sync.

I have other tools to handle adding new rows and deleting old rows.

Hope this explains things to your satisfaction. I need a tool to trap keyboard changes to column B of "Annual Budget" worksheet.

Any questions, please let me know.

John

[jaslake]

Leith...a bit more of an explanation. Upon a user update to column B of the AnnualBudget worksheet (which will be recognized by the change event macro associated with worksheet 6), I would anticipate trapping that change and make the same change on the categories worksheet. I am developing Macrotest for this purpose. I anticipate calling Macrotest at the time a change event takes palce. I honestly don't know how to do this yet as I've only been VBA'ing for about three weeks. But, I'll figure it out (or call on you guys .

Let me know if there is something that doesn't make sense or needs further clarification.

Thanks...John

[jaslake]

Well, I got this to work this morning. I deleted "Public OldValue As String" from the change event module and "OldValue" is now passed to Moduletest.

I thought I tried this yesterday but, obviously not.

Thanks for your help.

[davecooper@optonline]

Hello Leith- I realize this is an old thread but just wanted to thank you for posting your initial solution for capturing a cell value prior to worksheet_change event. It came in quite handy and works like a charm. And you are correct, the public declaration is required. -DC