Userform Formula Help Needed

  • Thread starter Thread starter timh2ofall
  • Start date Start date
T

timh2ofall

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)
 
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.
Click to expand...

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
 
Back
Top