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: Imelda    Date: Aug 14    Category: MS Office    Views: 941

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: Daw Boonliang     Answered On: Aug 14

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: Christie Bradley     Answered On: Aug 14

: 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.

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: Wendy Harrison     Answered On: Aug 14

I really appreciate your support  and help.
Automating office is real fun with VBA, and when there are guys like you
there helping and sharing its even better...