Real time textbox update in userform

[JohnBi]

Hello,
in a ws I have cells that are in real time updated.
Now I am able to get the value at the time I read the cell content with:

Dim Title As String
Title = UserForm2.ComboBox1.Value
TextBox2.Value = Application.WorksheetFunction.VLookup(Title, Worksheets("Setup").Range("C2:D115"), 2, False)

Is there a way to see the update value in the userform when the source cell is updated?
Thank you in advance for your help.
Regards
John

 

[6StringJazzer]

If you close the userform with Unload this may not work.

In the module for worksheet Setup add this code. This will update the textbox in the user form any time that data in C2:D115 changes.

Private Sub Worksheet_Change(ByVal Target As Range)

   Application.EnableEvents = False
   
   If Not Intersect(Target, Range("C2:D115")) Is Nothing Then
      UserForm2.TextBox2.Value = Application.WorksheetFunction.VLookup(UserForm2.ComboBox1.Value, Range("C2:D115"), 2, False)
   End If

   Application.EnableEvents = True

End Sub

 

[JohnBi]

Thank you for your answer.
I change the close code from "Unload Me" to "UserForm2.Hide", but the value of the TextBox2 in still not updated.

Regards
John

 

[6StringJazzer]

I suggest you do some troubleshooting and see if the Worksheet_Change code is executing when expected, and whether the line that updates the textbox is executing when expected. I can test my own code but I can't test it in the context of your file.

 

[Jaafar Tribak]

How are the cells being updated ? Try the Worksheet_Calculate event and see if that works.

 

[6StringJazzer]

Agree. If C2:D115 contain formulas and you are looking for changes in formula results, the Change event will not do that. You will have to reload the textbox on calculations. But you can't check for the range--so it will be any time there is any calculation.

Private Sub Worksheet_Calculate()

   Application.EnableEvents = False

   UserForm2.TextBox2.Value = Application.WorksheetFunction.VLookup(UserForm2.ComboBox1.Value, Range("C2:D115"), 2, False)

   Application.EnableEvents = True

End Sub

 

[JohnBi]

Hi,
Thank you for all your comments and suggestions. Most likely I'll add a button to refresh manually the userform TextBox2 before insert the value in the sheet.
Regards
John