The worksheet_activate will only get called when you change the sheet that is
selected in the workbook. the userform Initialize onle get called once after
a workbook gets opened and never again until the workbook is closed. Yo need
to move the code in the userform Initialize code to another location.

If your userform gets closed and open then put this code before the SHOW
method in the main code. If the userform remains opened then put it in the
userform activate function instead of the initialize function

Sub userform1_Activate()

"Richhall" wrote:

Hi, As per previous post I have some fields in a userform that do a
COUNTIF of values in a worksheet. However, as I change worksheet the
userform doesn't amend the values. I've tried unloading and reloading
the userform in the Worksheet_Activate but the values remain in there,
Ive also tried userform Activate and Initialise. How do I get the
values to refresh on changing worksheet please?

Private Sub Worksheet_Activate()

MyForm.Hide
Unload MyForm
Load MyForm
MyForm.Show

End Sub

Private Sub UserForm_Initialize()

Dim BananaBox, AppleBox, PearBox
Set ws = ActiveSheet

Me.BananaBox = Application.WorksheetFunction. _
CountIf(ws.Range("$G$9:$H$44"), "Banana") +
Application.WorksheetFunction. _
CountIf(ws.Range("$N$9:$O$44"), "Banana") +
Application.WorksheetFunction. _
CountIf(ws.Range("$U$9:$V$44"), "Banana") +
Application.WorksheetFunction. _
CountIf(ws.Range("$AB$9:$AC$44"), "Banana") +
Application.WorksheetFunction. _
CountIf(ws.Range("$AI$9:$AJ$44"), "Banana")

Me.AppleBox = Application.WorksheetFunction. _
CountIf(ws.Range("$G$9:$H$44"), "Apple") +
Application.WorksheetFunction. _
CountIf(ws.Range("$N$9:$O$44"), "Apple") +
Application.WorksheetFunction. _
CountIf(ws.Range("$U$9:$V$44"), "Apple") +
Application.WorksheetFunction. _
CountIf(ws.Range("$AB$9:$AC$44"), "Apple") +
Application.WorksheetFunction. _
CountIf(ws.Range("$AI$9:$AJ$44"), "Apple")

End Sub