S
salgud
I got help with this code from Wouter. It worked great. Then I replaced
variable and object names with less generic names that follow my standard
naming schema. And lost the connection between the fields and the list
boxes! Code now is:
Private Sub cboCode_Change()
Dim rCodes As Range
cboFacilities.Clear
For Each rCodes In Range("codeList").Cells
If rCodes.Text = cboCode.Text Then
cboFacilities.AddItem rCodes.Offset(0, 1).Text
End If
Next
End Sub
Private Sub usfFacilNameID_Activate()
Dim rCodes As Range
Dim iCodes As Integer
Dim bCodes As Boolean
Dim ws As Worksheet
Dim sFacilName As String
Set ws = ActiveSheet
cboCode.Clear
For Each rCodes In Range("codeList").Cells
bCodes = True
For iCodes = 0 To cboCode.ListCount - 1
If cboCode.List(iCodes) = rCodes.Text Then
bCodes = False
Exit For
End If
Next
If bCodes Then
cboCode.AddItem rCodes.Text
End If
Next
End Sub
Private Sub cbOK_Click()
Dim ws As Worksheet
Dim sFacilName As String
Dim iFacilID As Integer
On Error Resume Next
sFacilName = usfFacilIDName.cboFacilities
iFacilID = usfFacilIDName.cboCode
On Error GoTo 0
Set ws = ActiveSheet
If sFacilName = "" Then
MsgBox "Please select an ID No.", vbOKOnly
With Me.cboCode
' .SelStart = 0
' .SelLength = Len(.Text)
.SetFocus
End With
Else
Me.Hide
End If
ws.Range("C1").Value = sFacilName
End Sub
Does anyone see the disconnect between the columns on the spreadsheet, ID
in col A, Facil name in col B?
Thanks!
variable and object names with less generic names that follow my standard
naming schema. And lost the connection between the fields and the list
boxes! Code now is:
Private Sub cboCode_Change()
Dim rCodes As Range
cboFacilities.Clear
For Each rCodes In Range("codeList").Cells
If rCodes.Text = cboCode.Text Then
cboFacilities.AddItem rCodes.Offset(0, 1).Text
End If
Next
End Sub
Private Sub usfFacilNameID_Activate()
Dim rCodes As Range
Dim iCodes As Integer
Dim bCodes As Boolean
Dim ws As Worksheet
Dim sFacilName As String
Set ws = ActiveSheet
cboCode.Clear
For Each rCodes In Range("codeList").Cells
bCodes = True
For iCodes = 0 To cboCode.ListCount - 1
If cboCode.List(iCodes) = rCodes.Text Then
bCodes = False
Exit For
End If
Next
If bCodes Then
cboCode.AddItem rCodes.Text
End If
Next
End Sub
Private Sub cbOK_Click()
Dim ws As Worksheet
Dim sFacilName As String
Dim iFacilID As Integer
On Error Resume Next
sFacilName = usfFacilIDName.cboFacilities
iFacilID = usfFacilIDName.cboCode
On Error GoTo 0
Set ws = ActiveSheet
If sFacilName = "" Then
MsgBox "Please select an ID No.", vbOKOnly
With Me.cboCode
' .SelStart = 0
' .SelLength = Len(.Text)
.SetFocus
End With
Else
Me.Hide
End If
ws.Range("C1").Value = sFacilName
End Sub
Does anyone see the disconnect between the columns on the spreadsheet, ID
in col A, Facil name in col B?
Thanks!