VBA to record cell reference values as text and record change

**Katie O** · 01-23-2013, 06:13 PM

Hi,

I am a new to using VBA and was hoping that there was a simple solution to my issue. This is my first post so I appologize if the description is too long.

I need to record cell values (A1:A5) and paste them into empty cells. But, the cell values I need to copy are actually reference values to cells in a different sheet. I used the following VBA to automatically copy and paste the specific values anytime there was a change.

Please Login or Register  to view this content.

The code works well in a simple Excel worksheet (see attached). But, when I placed it my actual worksheet (which has many formulas), the macro runs repeatedly until there is a "Run-time error '28:Out of stack space" message.

My understanding is that the macro calculates every time there is a change in the Excel-which is well into the hundreds, even though I am only looking to capture changes in cells A1:A5. I have already tried to use a Change event instead of Calculate, to only looks for changes in specific cells. The code works perfectly when the cells are text. But, right now, my cells will always be reference values or formulas.

Please Login or Register  to view this content.

Is there a macro that could copy the cells and paste them as text, and then perform my change macro? Any other suggestions would be appreciated.

Thank you in advance for your help.

**Leith Ross** · 01-23-2013, 11:14 PM

Hello Katie O,

Welcome to the Forum!

The error occurs because your macro is creating a loop. When a cell calculates it triggers the Calculate event. The code then makes changes to the worksheet that cause the event to trigger again, and so on. To stop this from happening, you need to suspend event recognition by Excel. I have added the statements to your code to suspend and then re-enable these events.

Please Login or Register  to view this content.

**Katie O** · 01-24-2013, 12:14 PM

Hi Leith,

Thank you for your help and explanation. When I tested the macro the extra statement solved the looping issue.

There is just one issue I am having now. The values in A1:A5 are calculated from other cells that have data validation. For example, cells B1:B5 only allow entry of numbers between 1-10.

When I entered an incorrect value in one of the cells (ex. 100), the macro recorded my values in A1:A5 three times. It ran once when I put in an incorrect value, a second time when I clicked "Retry" on the data validation error message, and a third time when I put in my corrected value. Is there a way to have the macro only record the last step- and correct values?

Best,

Katie