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:$A L
$44,"Bananas")

Can anyone help please?

Cheers

Rich

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

"Richhall" skrev i meddelelsen
...
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:$A L
$44,"Bananas")

Can anyone help please?

Cheers

Rich

Brilliant thank you

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")

????

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