Text box to equal cell value in VBA Form

**tfuhr** · 10-03-2013, 07:59 PM

Hi all,

I am a bit of a novice in vba and am trying to fix an issue that I can't quite wrap my head around despite extensive research. I would love for you guys to give me your thoughtful advice.

I am basically working with a model that enables the user to change assumptions and get an updated forecast based on their new inputs. I have created a form as a front-end that has different labels and text-boxes referencing cells in the back-end excel worksheet. For example i have:

Please Login or Register  to view this content.

So when the user changes NonFarmCAGR1, he should see nonfarm1value grow or decrease accordingly. Now since it is a CAGR, I have formatted the nonfarmcarg1 textbox as a percentage instead of having it displaying a number with 16 decimals. using the following code:

Please Login or Register  to view this content.

So the problem I am running into is that when I have the event that says: when nonfarmcagr changes

Please Login or Register  to view this content.

, if the user just clicked and actually didn't change anything the value gets rounded and the forecast therefore slighty changes. For exemple if the base value is 0.0161616 that is displayed 1.62%, if the user click, the value in cell D6 will become
0.0162. I have try using if statements and rounds but I couldn't figure it out.

Additionally D6 actually reference another cell N6, and when the user click and doesn't actually change the value, it would be even better if the formula in D6 stayed "=N6" instead of becoming the value 0.0161616.

Any thoughts on what I could implement?

Thanks in advance,

Thi

FYI, I tried something like this, which wasn't quite working and seems over complicated to me:

Please Login or Register  to view this content.

**6StringJazzer** · 10-03-2013, 08:45 PM

I'm rather confused about your overall scenario. You are filling in the textbox based on the rounded value of D6, then if the textbox changes you are copying that rounded value back to D6. Well, when you fill in the textbox that triggers a change, therefore every time you copy D6 into the textbox the value will get copied back to D6. So why are you copying it from the textbox when you want the same value that's already there?

This problem would be much clearer if you could post your file and explain how this works from the user's point of view.

Edit: I read through this again, and got a little more out of it. The real problem is that when you put data into a text box, you see exactly what's in the box. It's not like when you have data in a cell but it can display a rounded value. So you need to have somewhere else to store the full value that you are showing in the text box. I suggest you use a variable declared as Double globally in the form code to mirror the value shown as rounded in the textbox. Posting your file would help.

**tfuhr** · 10-04-2013, 11:12 AM

Hey 6StringJazzer,

Thank you very much for your answer. I will look into using a variable declared as Double.

Unfortunately, I can't share with you the original file due to proprietary reasons. I have however put together a file that mimics it that I am attaching to that post.

Please feel free to give me your thoughts.

Thanks again,

Thi