Pulling cell values into text boxes in a userform upon selecting a certain combobox value

Hi, nashle6,

if you start working with VBA please take your time and have Google search for the Reddick Naming Conventions - IŽd never name a Textbox Frame, really never.

And I would take into consideration that any item should only appear once in a ComboBox. Last thing: I wouldnŽt place 2 Sets of Textboxes which are to be populated on the change of one ComboBox but to be altered on the change of a second - I would just use one set and fill that only after the name of a person has been selected and leave them empty otherwise.

HereŽs the code:

Private Sub ColourDrop_Change()
Dim lngCounter As Long
Dim lngCol As Long
Dim lngTB As Long
EntrantCB.Clear
For lngCounter = 1 To 2
  For lngCol = 1 To 3
    Controls("txtBox" & lngCounter & "_" & lngCol).Value = vbNullString
  Next lngCol
Next lngCounter
With Sheets("Scores")
  For lngCounter = 2 To .Cells(Rows.Count, "B").End(xlUp).Row
    If UCase(.Cells(lngCounter, "B").Value) = UCase(ColourDrop.Value) Then
      lngTB = lngTB + 1
      EntrantCB.AddItem .Cells(lngCounter, "A").Value
      For lngCol = 1 To 3
        Controls("txtBox" & lngTB & "_" & lngCol).Value = .Cells(lngCounter, 2 + lngCol).Value
      Next lngCol
    End If
  Next lngCounter
End With
End Sub


Private Sub UserForm_Initialize()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Range
Dim rngCell As Range

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Scores")

With wsSheet
  Set rnData = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
End With

With Me.ColourDrop
  .Clear
  For Each rngCell In rnData
    If WorksheetFunction.CountIf(wsSheet.Range("B2:B" & rngCell.Row), rngCell) = 1 Then
      .AddItem rngCell
    End If
  Next rngCell
  .ListIndex = -1
End With
End Sub

Ciao,
Holger