How do I create one variable on a sheet available to all macros and ActiveX buttons

[AndyTampa]

I have a spreadsheet I created to help me at work. It has templates for different things I do. On this sheet, I use macros to reset the templates. I recently discovered how to create ActiveX buttons to run their own code. What they do is update the text in a single cell either by replacing the text or appending it.

I'm trying to create a sort of Undo function. I want to make my macros copy the contents of this cell to a variable before making the changes. Then, if I hit the wrong button, I can hit an Undo Last Note button and put the original text back.

Somehow, I need to create a variable that exists when the worksheet is opened. I need the Reset macro which is in a module to be able to clear it. And I need the ActiveX buttons to be able to write to it, preferably by using a module level macro so that I only have to add one line to each of 18 ActiveX button's codes.

I have a feeling it's going to be something along the lines of "Option something something", but I don't understand those things yet. Nothing I've found mentions having a mixture of modules and activeX buttons.

 

[RoryA]

You would need a Public variable declared in a normal module. However, that will lose its value if any code errors occur or anything else causes a state loss, so it might be simpler to just write the value to another cell.

 

[AndyTampa]

You would need a Public variable declared in a normal module. However, that will lose its value if any code errors occur or anything else causes a state loss, so it might be simpler to just write the value to another cell.

I was reluctant to use another cell because of the size of the row changing but once I realized I could put it on the same row for them to change together, the decision was simpler. It only required one line of code in each button to copy the text in one cell to another. It's already done. Z18 = B18. Now all I need is another button that does B18=Z18.