How to Protect Calculated fields.

[Clarence Perdue Jr.]

I have a spread sheet that has Calculated Fields. I would like to protect the calculated fields. In my code I am using a vba code from the following site.

http://blog.contextures.com/archives/2010/10/01/new-improved-excel-data-entry-form/

When I use the navigate(View Records) to move off the record and it update the information on the cells the application crashes or removes the formula. The following code seem to be the problem. The line 6 seems to cause the problem with the cell calculation being removed.

1.) With historyWks
2.) lRec = inputWks.Range("CurrRec").Value
3.) If lRec > 0 And lRec <= lLastRec Then
4.) lRecRow = lRec + 1
5.) .Range(.Cells(lRecRow, lColHist), .Cells(lRecRow, lCellsDE)).Copy
6.) rngDE.Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True

rngA.Select​

End If​

End With

Do anyone have any suggestions.

 

[Luke M]

Code pastes info from LesseeData to column D of Input. Col D has not formulas...macro ran w/o crashing. Not sure what the problem is? Or, is there supposed to be calculations in col D (in which case, I apologize that my mind-reading did not pick up on that )?

 

[Clarence Perdue Jr.]

There is a problem when you do click the clear button within cell J12. Secondly, the formulas are removed when you move to another record using the View Records control button in cells H10 and J10. I am uploading a new file for testing. I am not sure how to ensure that the formula.

 

[Clarence Perdue Jr.]

Code pastes info from LesseeData to column D of Input. Col D has not formulas...macro ran w/o crashing. Not sure what the problem is? Or, is there supposed to be calculations in col D (in which case, I apologize that my mind-reading did not pick up on that )?

Test Data with formula for testing is in Row N

 

[Luke M]

Basic answer is, you will need to change your form layout. All input cells should be grouped together, and then formula cells. Don't mix them up.
The form view transfers constants, and formulas then read this information.

 

[Clarence Perdue Jr.]

r

Basic answer is, you will need to change your form layout. All input cells should be grouped together, and then formula cells. Don't mix them up.
The form view transfers constants, and formulas then read this information.

Basic answer is, you will need to change your form layout. All input cells should be grouped together, and then formula cells. Don't mix them up.
The form view transfers constants, and formulas then read this information.

The layout is the problem. Now, I understand why the formula field were below all the input fields. Now when you do the Special Paste it is only for the input fields and not the calculation fields. But, I am sure that I understand why the formula do not get overwritten with the values. Secondly, I would like to hear your opinion on form layout. I would like to make this form easy for the user. Thirdly, I am having a problem with the way the input and search work. I have a list of Name which will always be standard. How do I layout the form to handle search and have default input name selection. I tried putting a drop down but the application crash. Finally, I need to understand how to prevent duplicate. Currently, the form is looking for duplicate name. But, the name can be entered multiple times in a year. The only thing that I can think of for uniqueness is the Lessee Name, Agreement Period and Year. I am not sure I can count this being the unique combination because they may do multiple payment in a period. There are two periods within a year that the Lessee must make payment.

 

[Clarence Perdue Jr.]

r


The layout is the problem. Now, I understand why the formula field were below all the input fields. Now when you do the Special Paste it is only for the input fields and not the calculation fields. But, I am sure that I understand why the formula do not get overwritten with the values. Secondly, I would like to hear your opinion on form layout. I would like to make this form easy for the user. Thirdly, I am having a problem with the way the input and search work. I have a list of Name which will always be standard. How do I layout the form to handle search and have default input name selection. I tried putting a drop down but the application crash. Finally, I need to understand how to prevent duplicate. Currently, the form is looking for duplicate name. But, the name can be entered multiple times in a year. The only thing that I can think of for uniqueness is the Lessee Name, Agreement Period and Year. I am not sure I can count this being the unique combination because they may do multiple payment in a period. There are two periods within a year that the Lessee must make payment.

Again, I was using the following site as a starting point. I am new to doing Excel Vba development.
http://blog.contextures.com/archives/2010/10/01/new-improved-excel-data-entry-form/

 

[Clarence Perdue Jr.]

r


The layout is the problem. Now, I understand why the formula field were below all the input fields. Now when you do the Special Paste it is only for the input fields and not the calculation fields. But, I am sure that I understand why the formula do not get overwritten with the values. Secondly, I would like to hear your opinion on form layout. I would like to make this form easy for the user. Thirdly, I am having a problem with the way the input and search work. I have a list of Name which will always be standard. How do I layout the form to handle search and have default input name selection. I tried putting a drop down but the application crash. Finally, I need to understand how to prevent duplicate. Currently, the form is looking for duplicate name. But, the name can be entered multiple times in a year. The only thing that I can think of for uniqueness is the Lessee Name, Agreement Period and Year. I am not sure I can count this being the unique combination because they may do multiple payment in a period. There are two periods within a year that the Lessee must make payment.

 

[Luke M]

Hi Clarence,

Just to warn you, the improvements you are asking for are much more complex than a data input form, especially with the setup you have currently. If you need to control for unique entires, I would first suggest that each record needs a unique identifier (e.g., transaction #, account #, date/time stamp, etc.). Once you have a key, do a quick search here on for different data entry techniques. I'd still encourage you to learn about these techniques, just wanted to preface it by saying that it will take some work. Some articles to get you started:
http://chandoo.org/wp/2009/10/26/duplicate-data-entry/
http://chandoo.org/wp/2011/02/07/data-entry-forms-with-conditional-formatting-and-validation/