range value displayed in a textbox (controlsource) VBA

Question

hello,

I am trying to get a text box on a userform to always display the value of the last cell in column C, when a new number is entered the "named" formula (=INDEX(C7:C25,COUNT(C7:C25)) passes the value to the textbox  via its controlsource property .

However, the change is not reflected strightaway, I need to close the userform an reopen it to see the new value?

Thanks

Answer 1

If you are using the userform to insert the value into the worksheet that creates the value in column C then use the command button that inserts the value to update the textbox.

e.g.

Private Sub CommandButton1_Click()

Dim xlSheet As Worksheet

Set xlSheet = ActiveWorkbook.Sheets(1)

'Do stuff

Me.TextBox1.Value = xlSheet.Range("C" & xlSheet.Range("C" & xlSheet.Rows.Count).End(-4162).Row)

End Sub

Answer 2

Ok. Thanks

Was wondering thought, If I set a TextBox Properties

ControlSource=Sheet1!C9

and that C9 contains a formula like =$D$3-SUM($C$7:$C$8)

Why do I get the correct value in the textbox but the formula in C9 disappears when C9 changes  value?