real time updating of textbox values in a userform

[Arie Bos]

To all the Masters...
I have a user form where I want to show a number in a textbox 'txtN_EnergyCostsMonth' which is the product of some filled in numbers of 4 other textboxes. Some of these are read from a range in a worksheet, and some or filled in this specific user form.
I am struggling with the proper method how to have that number in txtN_EnergyCostsMonth updated in real time e.g. the number shows OK, but if one of the text boxes is changed, it does not update.

The code I use is:

Private Sub UserForm_Initialize()
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
   txtN_EnergyCostsMonth.Value = Format(Range("SSD_EnergyUse").Value * Val(txtN_EnergyUnitCost.Value) * _
Val(txtN_OpsDays_SSD.Value) * Val(txtN_HoursDay_SSD.Value) / 12, "#,##")
End sub

What I would like is when I change the value in e.g. txtN_EnergyUnitCost, the value of txtN_EnergyCostsMonth is automatically updated.

 

[GWteB]

You could use the Change event of the text boxes involved for that ...

Your code could look like this...

Private Sub txtN_EnergyUnitCost_Change()
    Update
End Sub

Private Sub txtN_OpsDays_SSD_Change()
    Update
End Sub

Private Sub txtN_HoursDay_SSD_Change()
    Update
End Sub

Private Sub Update()
    txtN_EnergyCostsMonth.Value = Format(Range("SSD_EnergyUse").Value * _
                                       Val(txtN_EnergyUnitCost.Value) * _
                                          Val(txtN_OpsDays_SSD.Value) * _
                                         Val(txtN_HoursDay_SSD.Value) / _
                                         12, "#,##")
End Sub

 

[Arie Bos]

Hi GWteB,
a very elegant solution and it works perfectly. Thank you for picking up on this!

 

[GWteB]

You are welcome and thanks for the feedback.