Formulas used in spreadsheet to be written in code to go in Userform module

**Stratfordoaks** · 11-25-2013, 06:21 AM

I have a userform that has text boxes that link to formulas in cells in spreadsheet to gain their value. It works ok, but I would like to start placing the calculation formulas into the userform textboxes themselves. I can see how to do simple sum and minus between textboxes but have no idea how to go about the formula below:

The formula on spreadsheet that populates TextBox4 is:

=IF(D83=-65,"0.00",+D65+SUM(D35:D37)+D83-SUM(D39:D48)-D64)

The cells above are represented by these textboxes on the userform:

D83 = TextBox97
D65 = TextBox55
D35 = TextBox28
D36 = TextBox29
D37 = TextBox30
D39 = TextBox31
D40 = TextBox32
D41 = TextBox33
D42 = TextBox34
D43 = TextBox35
D44 = TextBox36
D45 = TextBox37
D46 = TextBox38
D47 = TextBox39
D48 = TextBox40
D64 = TextBox54

Any help would be most wecome

Stratfordoaks

**Richard Buttrey** · 11-25-2013, 06:32 AM

Hi,

It's not clear, at least to me, what you mean by "placing the calculation formulas into the userform textboxes themselves". Are you wanting to see a string of text that describes the formula in the text box or a resultant value.

Please upload your workbook and tell us what is your overall aim. We usually like to approach these things from first principles since there is often a simpler and more efficient way than the one presented.

**hudibyk** · 11-25-2013, 06:45 AM

You can't write a formula, that will be operate on textboxes. The best way is write UDF, for example:

Function MyFormula (TBox83 MSForms.TextBox, TBox97 as MSForms.TextBox, TBox65 as MSForms.TextBox, TBox28 as MSForms.TextBox ......) as Double

If TBox83.Value = -65 Then
      MyFormula = 0
Else
      MyFormula = TBox65.Value + TBox35.Value + TBox36.Value ......
end If

End Function

The second way is write UDF, that will be operate on spreadsheet, where you can use spreadsheet's functions:

Function MyFromula (rng83 as Range, rng65 as Range, rng35_37 as Range .......) as Double
If rng83.Value = -65 Then
     MyFormula = 0
Else
     MyFromula = rng65.Value + WorksheetFunction.Sum(Range(rng35_37.Address)) + rng83.Value - WorksheetFunction.Sum(Range(rng39_48.Address)).......)
End If
End Function

Of course there is much more solutions.

**Stratfordoaks** · 11-25-2013, 06:55 AM

Thank you for your reply Richard. Yes I agree if you see what I have there may be a simpler solution. I have attached the workbook which with the form is used for cashing up purposes at our club. At the moment Userorm1 is used to populate the cells in sheet "Daily". This sheet has varios formulas in cells which sum up totals but also provide the "checks" to see if the till has balanced. A macro is run to post the data in "Daily" to the other sheets in the Workbook.

Eventually my aim is to do away with sheet "Daily" and the Userform1 to populate the relevant worksheets when the macro runs. When the form opens go to the last page and enter "Admin" with the password "1". Worksheet "Daily" can be unprotected with password "1"

Kind regards

Nigel
Stratfordoaks