S
salgud
I have a userform which is supposed to find a value in column A, then find
all corresponding values in col B. Somehow, it's not working. Here is the
code:
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!
all corresponding values in col B. Somehow, it's not working. Here is the
code:
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!