Whitty
08-10-2007, 07:31 AM
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.
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.