real time updating of textbox values in a userform

Arie Bos

Board Regular
Joined
Mar 25, 2016
Messages
173
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,158
Office Version
  1. 2013
Platform
  1. Windows
You could use the Change event of the text boxes involved for that ...

VBA_VBE_ControlEvents.gif


Your code could look like this...
VBA Code:
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

Board Regular
Joined
Mar 25, 2016
Messages
173
Office Version
  1. 365
Platform
  1. Windows
Hi GWteB,
a very elegant solution and it works perfectly. Thank you for picking up on this!
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,158
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for the feedback.
 

Forum statistics

Threads
1,144,528
Messages
5,724,859
Members
422,586
Latest member
nassardewa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top