I have a spreadsheet in which Sheet1(X1:X100) contains a list of
unique names. On my Userform I have ComboBox1 and TextBox1. ComboBox1
has it's rowsource as Sheet1(X1:X100). My Data
Sheet2(A2:A500)contains rows of the names inputted from ComboBox1.
I am trying to get TextBox1 on my Userform to count the number of
times the same name occurs in range Sheet2(A2:A500)when a change
occurs in ComboBox1.
What is the Sub formula I can use for this change() event?
Any help is appreciated.
Tim

Tim,

This should do it

Private Sub ComboBox1_Change()
TextBox1.Text = WorksheetFunction.CountIf(Worksheets("Sheet2"). _
Range("A2:A500"), ComboBox1.Value)
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"timh2ofall" wrote in message
. ..
I have a spreadsheet in which Sheet1(X1:X100) contains a list of
unique names. On my Userform I have ComboBox1 and TextBox1. ComboBox1
has it's rowsource as Sheet1(X1:X100). My Data
Sheet2(A2:A500)contains rows of the names inputted from ComboBox1.
I am trying to get TextBox1 on my Userform to count the number of
times the same name occurs in range Sheet2(A2:A500)when a change
occurs in ComboBox1.
What is the Sub formula I can use for this change() event?
Any help is appreciated.
Tim

I have a spreadsheet in which Sheet1(X1:X100) contains a list of
unique names. On my Userform I have ComboBox1 and TextBox1. ComboBox1
has it's rowsource as Sheet1(X1:X100). My Data
Sheet2(A2:A500)contains rows of the names inputted from ComboBox1.
I am trying to get TextBox1 on my Userform to count the number of
times the same name occurs in range Sheet2(A2:A500)when a change
occurs in ComboBox1.
What is the Sub formula I can use for this change() event?
Any help is appreciated.

When you change ComboBox1 make sure the change event copies the new name to
a worksheet cell, sheet2(A501). In sheet2(A502) add this:

countif("A2:A500",A501)

Set TextBox1 to display the contents of sheet2(A502)

HTH

Chris