MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Output in VBA Forms

Posted by Iain on August 25, 2001 7:35 AM

I have several inputs in a form..
lets say for simplicity input1 through input3 linked to cells A1:A3 using ControlSource
In sheet Cell A4 I need =sum(A1:A3)

What I would like to do is to show the sumation results from Cell A4 in the form as a total of A1:A3
I have tried to use a textbox and listbox set as False and True but each time the form is used the =Sum(A1:A3) is over written
Can any of you guys tell me how to show the sum of A1:A3 in the form which is not only protected so that it cannot be overwriten with an insert but to show the results from the worksheet A4
As usual many thanks for your help

Posted by Robb on August 25, 2001 6:16 PM


Try this"

-Put the SUM formula in A4
-Put a TextBox (say TextBox4) on your form
-Set enabled for TextBox4 to False
-Do NOT set the ControlSource for the TextBox4
-Set the Text in TextBox4 via the AfterUpdate event in each of the other TextBoxes (1 to 3??)

The code in the UserForm should be something like this
(just copy and change the various references to suit):

Private Sub TextBox1_AfterUpdate()
TextBox4.Text = Worksheets("Sheet1").Range("A4")
End Sub
Private Sub TextBox2_AfterUpdate()
TextBox4.Text = Worksheets("Sheet1").Range("A4")
End Sub
Private Sub TextBox3_AfterUpdate()
TextBox4.Text = Worksheets("Sheet1").Range("A4")
End Sub

Now, whenever you change one of the other values, TextBox4 should reflect the value
of A4.

Any help?