MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Convert string like "=123+456+789" to Long and then calculate

  Asked By: Carole    Date: Aug 31    Category: MS Office    Views: 979

I have develop a user Data Entry form for my spreadsheet document. which as
usual takes values from user and simply put them on appropriate cells. job

Now on my form there is a field which takes sum of amounts as
"=200+786+900+3" etc. I want to place another text field which should or
would show me the sum before placing the entered data on worksheet.

Problem is, it does not.

Question is, how this task can be accomplish,

NOTE: For more simpler version of my problem one can view attached bitmap
file, as I am not always able to convey my message clearly.

I will realty appreciate your support.

Note: when i assign the value of amount text field to the some variable to
calculate the sum; it gives me some "type-mismatched" error. I have tried
things like "CDBL, CSTR, CLNG etc".



3 Answers Found

Answer #1    Answered By: Milind Mishra     Answered On: Aug 31

The problem  seems to be you are attempting to add strings together to get a sum.
What I have done in the past is to assign  each of your input values  to a dummy
variables and to DIM each of these variables as Long. sum  the dummy variables
and assign the new value into your sum text  box. I run a macro to sum the data
whenever the input boxes are changed.

Also, I have an "update worksheet  button" so the sum is updated in the
database only when I'm ready.

I know there is has to be a cleaner way of doing this, and I look forward to
seeing more replies; but this will put  you on the right path.

Answer #2    Answered By: Jamie Williams     Answered On: Aug 31

Use Evaluate(). Draw a new form  with two text  boxes on it
and add this sub to the code. Type your expression (=200+786+900+3)
into TextBox1 and click the Tab key.

Private Sub TextBox2_Enter()
TextBox2 = Evaluate(TextBox1.Value)
' or TextBox2 = Evaluate(TextBox1.Text)
End Sub

Answer #3    Answered By: Darrell Harvey     Answered On: Aug 31

Automating office is real fun with VBA, and when there are guys like you
there helping and sharing its even better...