Reopened: Cell contents erased by user form

[Whitty]

I had to reopen this case as the solution Jimmy came up with only worked briefly. Here is my original post:


I have a fairly extensive user form that will gather data from a user then display a lot of analysis of that data. So a majority of the text boxes will simply display the results of formulas run in the worksheet based on the data that is inputted. I learned how to link a text box to the cell using ControlSource, but that did not solve my problem.

The problem is that when I put a formula into the cell linked to a text box in the user form, the formula runs right after I input it (like it normally would) but then the formula is overwritten with the results. So I can not pre-program all of the formulas and then run the form as all of the formulas are gone.


Jimmy replied with:


If you have the worksheet protected, the formula will remain even after the userform was displayed. And the formula works with protected sheet, too.
In order to be able to enter data manually into some cells in the sheet, you need to unlock those cells before protecting the sheet.
It might make sense to unlock all cells, then lock only those that contain formulas linked to textboxes.


So I protected the sheet and it worked briefly. However, even with the cells protected and the protection turned on, my formulas and whatever other cell content is in there is erased whenever the form is launched.

Let me further define how I need to use the form I created.


The form will be used to gather user inputs and place the results into cells. Formulas will run on the inputs and create outputs. The outputs will be displayed in the user form. The overall intent is to gather the inputs and view the analysis of those inputs right in the user form. This is not a one time use user form. Worksheets are stored, reopened and the data is updated from time to time.

To summarize, the intent of my user form is simply a "window" to the spreadsheet whereby data is passed to cells, remains displayed in the user form text box, can be changed, and calculations done on those inputs are placed into cells that are linked to the form and displayed in the user form.

Please help as this problem renders the form completely unusable.

[Norie]

As far as I'm aware you can't use cells with formulas in them as the ControlSource without the formulas being overwritten eventually.

[Bob Phillips]

Don't bind the textbox to the cell, it is more bother tahn it is worth. Leave the formulae, let them do their stuff, and just retireve it when you need it and put it in the Textbox.

[Whitty]

Well, I'll have to convert the user form to be input only then view the results of the analysis back in the workbook. That's too bad as it would have been very slick to use on user form to input the data and to view the resulting analysis of that data. I guess Excel is just not all that robust.

Thanks for the help.

[Bob Phillips]

Excel is robust, you are just trying to tell it to do two conflicting things.