Refresh textbox when data changes

[freddie5]

Hi All

Apologies for what may be a stupid question, but I am new to VBA, and have never posted to a forum before.

I have 2 worksheets, the first I use to allocate cost centres to different people and the second puts these people into different teams.

I have a userform with textboxs that shows a summary of the two sheets, which remains visible when switching between the two worksheets.

However, when any changes are made on the worksheets the userform does not update unless I close it and re open it.

I have put a refresh button on it which simply reopens the userform but would prefer it to update as and when changes are made.

Any help would be most appreciated.

 

[xlbo]

You need to hook into the WORKSHEET_CHANGE event and use that to update the data in the form.

The worksheet change event can be found by right clicking on the sheet tab and choosing "View Code"

Then select "worksheet" from the top left drop down box and "change" from the dd box on the right side. Code would go in there

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DaveInIowa]

You can hook into worksheet change events directly from your form's code:

Private WithEvents ws1 As Worksheet
Private WithEvents ws2 As Worksheet

Private Sub UserForm_Initialize()

' Set ws1 thru wsN to point to all worksheets you want to track ...
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")

End Sub

Private Sub ws1_Change(ByVal Target As Range)
    ' Update your form's data
End Sub

Private Sub ws2_Change(ByVal Target As Range)
    ' Update your form's data
End Sub