On Thu, 18 Mar 2010 15:15:01 -0600, salgud wrote:

On Thu, 18 Mar 2010 12:58:25 -0700 (PDT), Wouter HM wrote:

Hi salgud,

I would say Keep It Simple and use your first idea.

To give you a starting point I have created a sheet with 2 columns

1 | A1
2 | B1
2 | B2
3 | C1
4 | D1
5 | E1
6 | F1
6 | F2
6 | F3
6 | F4
7 | G1
8 | H1

The first column contaning a numiric code
The second containing the facilities.

Next I gave the first column a name: "codeList"

The I created a userform with two pulldown controls:
cboCode and cdoFacilitie

Finaly I created code to populate the control:

Private Sub cboCode_Change()
Dim rngCodes As Range

cboFacilities.Clear

For Each rngCodes In Range("codeList").Cells
If rngCodes.Text = cboCode.Text Then
cboFacilities.AddItem rngCodes.Offset(0, 1).Text
End If
Next

End Sub

Private Sub UserForm_Activate()
Dim rngCodes As Range
Dim intCodes As Integer
Dim blnCodes As Boolean

cboCode.Clear

For Each rngCodes In Range("codeList").Cells
blnCodes = True
For intCodes = 0 To cboCode.ListCount - 1
If cboCode.List(intCodes) = rngCodes.Text Then
blnCodes = False
Exit For
End If
Next
If blnCodes Then
cboCode.AddItem rngCodes.Text
End If
Next
End Sub

HTH,

Wouter

Thanks for the prompt reply. It works when I just run your code (pretty
amazing), but when I add some code to capture the facility name (same code
I've used before with variable names changed), it give me a "null" error.


Private Sub UserForm_Activate()
Dim rngCodes As Range
Dim intCodes As Integer
Dim blnCodes As Boolean
Dim ws As Worksheet
Dim sFacilName As String

Set ws = ActiveSheet
sFacilName = UserForm1.cboFacilities <---NULL ERROR

cboCode.Clear

For Each rngCodes In Range("codeList").Cells
blnCodes = True
For intCodes = 0 To cboCode.ListCount - 1
If cboCode.List(intCodes) = rngCodes.Text Then
blnCodes = False
Exit For
End If
Next
If blnCodes Then
cboCode.AddItem rngCodes.Text
ws.Range("C1").Value = sFacilName
End If
Next


End Sub

Somehow I thought that that line of code just set the string variable equal
to the output from the input box. Of course, it's null until you get the
form filled in, but that was the same on the others I worked with. Any
ideas/suggestions?

Played with it a while longer, got it working. Thanks for the help!