Userform Refresh on Worksheet change

[Richhall]

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

 

[Per Jessen]

Hi

The Worksheet_Activate will only fire, if you activate another sheet and
then activate the current sheet again.

How do you change the worksheet?

Regards,
Per

 

[Joel]

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]

Hi thanks, I am switching pages by clicking the worksheet tabs, but
the userform just remains within the workbook with the same values. I
tried Userform_Activate, but I don't think the userform is getting re-
activated on clicking a new worksheet, it just remains despite me
putting unload and load again.

All I can think is maybe putting the following in Worksheet_Activate?

UserForm.AppleBox = Application.WorksheetFunction. _
CountIf(ws.Range("$G$9:$H$44"), "Apple") +

Cheers

Rich