I'm planning work on a program in which the user enters a numeric code and
gets back a list of the facilities that code applies to. Usually, there
will be just one, sometimes as many as four. I'd like to design for 5 to be
safe. After they select the correct facility from the list, the code gives
a weighting value to other information they've entered and "scores" each
facility.

I've thought of 2 approaches, but am open to others. The first would be to
give the user an ad-hoc pull down list to select the correct one from. The
other method would be to construct a user form with the appropriate number
of choices so they can pick one. Don't know how easy/hard it is to create a
userform from code, particularly one in which the number of text boxes and
checkboxes vary. An alternate to this one would be to always create a
userform with 5 text/check boxes and just fill the number that are relevant
in this instance. How hard is that?

At first glance, I think the first method with the ad-hoc pull down list
would probably be easier, but I haven't done either so I'm not sure.

Am open to any and all suggestions. Thanks in advance.

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

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?

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!