Excel TextBox Calculation

[momo007]

Hi all, I have an Excel form with a number of textboxes (eg, textbox1, textbox2, textbox 3 etc).
I would like to add a textbox at the bottom of the form to automatically sum the above textboxes...

eg bottomTextBox = textbox1 + textbox2 + .... etc

how could I do this without a button? Not sure which textbox event this is related to...

Thanks for your help in advance!

 

[Norie]

It all depends when you want to do the sum.

If you want to do it once a value has been entered into a particular textbox then you should probably use that textbox's Exit event to do the calculation.

 

[momo007]

I need the bottom textbox to automatically update the total whenever the value of any of the other textboxes have been changed....

 

[momo007]

Nobody has any ideas??

 

[Tom Urtis]

You say you have a number of textboxes, textbox1, textbox2, textbox 3 etc, and so the first question is, what do you mean by "a number of" and "etc"? Just say it, how many do you have and what are their names? And what is your userform's name?

The reason why this is the first issue is, it'd be good to know whether you need a class module if you have 200 text boxes, or not if you have just 3 or 4. I would not use the Exit event in any case, just a Change event for each one (or a class event for all) to have the "sum" text box show the calculation.

Which brings us to the last point, why do you want to use a text box to show the sum when a label caption would be a better control, if all you want to do is show the sum.

 

[momo007]

Thanks Tom, didn't think of doing it in labels - may try this option.

The number of textboxes is not fixed, i use VB to generate these textboxes depending on another user input. Say the user wants to enter 10 items, then there'll be 10 textboxes. I'll need to sum these textboxes to give a total.

Any other suggestions please let me know! Thank you all for your help.

 

[Tom Urtis]

I assume you are using an Integer variable based on some factor to create the TextBoxes, because you'd need to know how many to create. Can you post the code you are using to create those textboxes on the fly, and can you post the name of your userform.

 

[momo007]

For counter = 1 To numClasses
Set tbAcB = frmEntry.mainFrame.Controls.Add("forms.textbox.1")
With tbAcB
.Left = margin + 230
.Top = (curRow * 14)
.Height = 15
.Width = 50
.Name = "drText" & curRow
'.Value = counter '******
End With
curRow = curRow + 1
Next counter

** mainFrame is the name of the frame.
** numClasses is the number of textboxes to be created.

Thanks Tom.