Prev: cell to have comma-delimited values based on text
Next: Urgent Help needed!!!!
From: salgud on 18 Mar 2010 18:30
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!
From: salgud on 22 Mar 2010 18:27
On Thu, 18 Mar 2010 16:30:54 -0600, salgud wrote:

> 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!

Is this super difficult?
 | 
Pages: 1
Prev: cell to have comma-delimited values based on text
Next: Urgent Help needed!!!!