How to update textbox from other textboxes

[chris0374]

Hi,

This is a project I'm working on for school. I'm trying to figure out how to have a textbox in a form update itself as I enter numerical values in other textboxes.

This is the code for the module which is doing all the work.

Option Explicit
..........
..........
.........


'average five simmuations for a given stock
ArrayofChosenAssetsInvestment(i) = ((Simulation(1) + Simulation(2) + Simulation(3) + Simulation(4) + Simulation(5)) / 5)

End If

'output results to spreadsheet
Range("H2").Value = "Initial"
Range("H3").Value = "Final"
Range("H1").Offset(0, i).Value = ArrayOfChosenAssets(i)
Range("H2").Offset(0, i).Value = InvestmentInitialValue(i)
Range("H3").Offset(0, i).Value = ArrayofChosenAssetsInvestment(i)
Range("n2") = Range("i2").Value + Range("j2").Value + Range("k2").Value + Range("l2").Value + Range("m2").Value
Range("n3") = Range("i3").Value + Range("j3").Value + Range("k3").Value + Range("l3").Value + Range("m3").Value
Range("n4") = (Range("n3").Value - Range("n2").Value) / Range("n2").Value
Next i


'build final message
message = "Final Portfolio Values:" & vbNewLine & vbNewLine

If frmInput.ListBoxAsset1.Value = 0 Or frmInput.InvestAsset1.Value = 0 Then
message = message & ""
Else
message = message & Range("i1").Value & " had an initial value of: " & Format(Range("i2").Value, "currency") _
& " and a final value of: " & Format(Range("i3").Value, "currency") & vbNewLine
End If

If frmInput.ListBoxAsset2.Value = 0 Or frmInput.InvestAsset2.Value = 0 Then
message = message & ""
Else
message = message & Range("j1").Value & " had an initial value of: " & Format(Range("j2").Value, "currency") _
& " and a final value of: " & Format(Range("j3").Value, "currency") & vbNewLine
End If

If frmInput.ListBoxAsset3.Value = 0 Or frmInput.InvestAsset3.Value = 0 Then
message = message & ""
Else
message = message & Range("k1").Value & " had an initial value of: " & Format(Range("k2").Value, "currency") _
& " and a final value of: " & Format(Range("k3").Value, "currency") & vbNewLine
End If

If frmInput.ListBoxAsset4.Value = 0 Or frmInput.InvestAsset4.Value = 0 Then
message = message & ""
Else
message = message & Range("l1").Value & " had an initial value of: " & Format(Range("l2").Value, "currency") _
& " and a final value of: " & Format(Range("l3").Value, "currency") & vbNewLine
End If

If frmInput.ListBoxAsset5.Value = 0 Or frmInput.InvestAsset5.Value = 0 Then
message = message & "" & vbNewLine & vbNewLine
Else
message = message & Range("m1").Value & " had an initial value of: " & Format(Range("m2").Value, "currency") _
& " and a final value of: " & Format(Range("m3").Value, "currency") & vbNewLine & vbNewLine
End If

message = message & "Predicted Final Portfolio Value (after 10 years) = " & Format(Range("n3").Value, "currency") & vbNewLine
message = message & "Predicted Portfolio Gain = " & Format(Range("n4").Value, "percent")

MsgBox message, vbInformation, "Simulated Portfolio Value"

End Sub

And this is the code for the form.

Option Explicit
Public cancelled As Boolean

Sub btnExit_Click()
End
End Sub
Sub btnExecute_Click()
cancelled = False
frmInput.Hide
End Sub

Private Sub btnReset_Click()

'Default List Box Selection
With frmInput
.ListBoxAsset1.ListIndex = 0
.ListBoxAsset2.ListIndex = 0
.ListBoxAsset3.ListIndex = 0
.ListBoxAsset4.ListIndex = 0
.ListBoxAsset5.ListIndex = 19 'should be CASH

'Default Investment Values for each Asset
.InvestAsset1.Value = 0
.InvestAsset2.Value = 0
.InvestAsset3.Value = 0
.InvestAsset4.Value = 0
.InvestAsset5.Value = 100000 'Put all in CASH initially
End With
End Sub

Private Sub txtTotal_Change()

'update textbox as values are entered i
InvestAsset1 = txtTotal
InvestAsset2 = txtTotal
InvestAsset3 = txtTotal
InvestAsset4 = txtTotal
InvestAsset5 = txtTotal

End Sub

Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
End
End Sub

The textbox that I want updating is txtTotal while the textboxes where I will be typing up the values are investAsset1, investAsset2, investAsset3, investAsset4, and investAsset5.

This project is supposed to pull data from an Access database and do a financial calculation. I have almost everything figured out except having the totaltextbox add itself with the values in investAsset textboxes as I type in the values.

Any help would be appreciated. Thank you!

[wamp]

I would assume something like

Please Login or Register  to view this content.

and so forth..

However, it would be a lot easier if you could post the workbook as an example, so we see what code goes where and what the controls are called

[chris0374]

Here is the Excel Spreadsheet with the code. Thank you for the help.

[chris0374]

I figured it out. Thanks for everything.