Add COUNTIF TO USERFORM TEXTBOX

[Richhall]

Apologies, I am not any sort fo expert in Visual Basic. I have a
userform which has a number of text boxes, on activation of the
userform I want the text boxes to calculate a COUNTIF based on the
worksheet that is open.

i.e

Form

Bananas [TEXT BOX]
Apples [TEXT BOX]
etc
etc

and the form code has something like the idea of (sorry no idea about
syntax)

set ws = active.worksheet
Bananas = Applicaiton.WorksheetFunction.COUNTIF("ws"!$A$9:$AL
$44,"Bananas")

Can anyone help please?

Cheers

Rich

[Per Jessen]

Hi Rick

Insert this code in the codesheet for the userform, where TextBox1 is the
name of the textbox.

Private Sub UserForm_Initialize()
Set ws = ActiveSheet
Me.TextBox1 = Application.WorksheetFunction. _
CountIf(ws.Range("$A$9:$AL$44"), "Bananas")
End Sub

Hopes this helps.
...
Per

Post by Richhall
Apologies, I am not any sort fo expert in Visual Basic. I have a
userform which has a number of text boxes, on activation of the
userform I want the text boxes to calculate a COUNTIF based on the
worksheet that is open.
i.e
Form
Bananas [TEXT BOX]
Apples [TEXT BOX]
etc
etc
and the form code has something like the idea of (sorry no idea about
syntax)
set ws = active.worksheet
Bananas = Applicaiton.WorksheetFunction.COUNTIF("ws"!$A$9:$AL
$44,"Bananas")
Can anyone help please?
Cheers
Rich

[Richhall]

Brilliant thank you

[Richhall]

How would I add numerous calculations from countifs in a different
range? i,e


Bananas + Apples.

Me.TextBox1 = Application.WorksheetFunction. _
CountIf(ws.Range("$A$9:$AL$44"), "Bananas") +
Application.WorksheetFunction. _
CountIf(ws.Range("$B$9:$D$44"), "Apples")

????

[Richhall]

Sorry, Have seen this works by trying, I'd missed some brackets out.