Hi,

Using Excel 2000, I have two worksheets: tblCurstomerApproved, and
tblContacts, populated from an access database.

Here's a sample of the tblCurstomerApproved data:

id Customer
7 AEROSPACE SYSTEMS
5 AERO-TECH ENGINEERING


Here's a sample of tblContacts data:
CoId CntctId Email FName LName Wphone
5 35 Bob Li 858-748-7301
5 224 Rick Jorgenson 858-748-7301
858-748-7319
7 84 Marsha Benter 507-235-3355 224 218-333-0112
7 312 Joan Jet 507-235-3355 260 218-333-0112

I accidentally sent this before I was finished.

I'm attempting to communicate the data structure. Basically, both
tables have the company id.

I have two combo boxes: cboPrimary and cboSecondary.

I want to use the company id when a user selects a company to display
the contacts in cboSeconday.

I tried this but it fails with a compile error.

Private Sub cboPrimary_Change()

With Me.cboSecondary
.RowSource = "Contacts"
If .Column(1).Value = cboPrimary.Column(1).Value Then
.AddItem = cboSecondary.Column(2)




End If
End With

I appreciate your comments.

Thanks,

Dan

I would think just looping through that range looking for a match would be
sufficient:

Option Explicit
Private Sub cboPrimary_Change()

dim wks as worksheet
dim myCell as range
dim myRng as range

set wks = worksheets("sheetnamewithtablehere")
set myrng = .range("a2", .cells(.rows.count,"A").end(xlup))
end with

With Me.cboSecondary
.RowSource = "" 'can't use .rowsource and .additem!
.clear
for each mycell in myrng.cells
if lcase(mycell.text) = lcase(me.cboprimary.value) then
.additem mycell.offset(0,1) '1 column to the right
end if
next mycell
end with

End Sub

(Untested, uncompiled. Watch for typos!)

dan dungan wrote:

I accidentally sent this before I was finished.

I'm attempting to communicate the data structure. Basically, both
tables have the company id.

I have two combo boxes: cboPrimary and cboSecondary.

I want to use the company id when a user selects a company to display
the contacts in cboSeconday.

I tried this but it fails with a compile error.

Private Sub cboPrimary_Change()

With Me.cboSecondary
.RowSource = "Contacts"
If .Column(1).Value = cboPrimary.Column(1).Value Then
.AddItem = cboSecondary.Column(2)

End If
End With

I appreciate your comments.

Thanks,

Dan

--

Dave Peterson

Hi Dave,

I pasted your code sample in cboPrimary. There was an orphan "end
with" that I removed. The line below highlights "(.rows" and returns
the error: Invalid or unqualified reference.

I have searched the archives for "Set myRng = .Range" to see if
something was misspelled, but I couldn't figure out the error.

Do you have any suggestions.

Thanks,

Dan

Here's the line that returned the error:
Set myRng = .Range("a2",.Cells(.Rows.Count,"A").End(xlUp))

Private Sub cboPrimary_Change()

Dim wks As Worksheet
Dim myCell As Range
Dim myRng As Range

Set wks = Worksheets("tblContacts")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))

With Me.cboSecondary
.RowSource = "" 'can't use .rowsource and .additem!
.Clear
For Each myCell In myRng.Cells
If LCase(myCell.Text) = LCase(Me.cboPrimary.Value) Then
.AddItem myCell.Offset(0, 1) '1 column to the right
End If
Next myCell
End With

End Sub

Typo in my suggestion:

set wks = worksheets("sheetnamewithtablehere")
set myrng = .range("a2", .cells(.rows.count,"A").end(xlup))
end with

Should have been:

set wks = worksheets("sheetnamewithtablehere")

With Wks
set myrng = .range("a2", .cells(.rows.count,"A").end(xlup))
end with



dan dungan wrote:

Hi Dave,

I pasted your code sample in cboPrimary. There was an orphan "end
with" that I removed. The line below highlights "(.rows" and returns
the error: Invalid or unqualified reference.

I have searched the archives for "Set myRng = .Range" to see if
something was misspelled, but I couldn't figure out the error.

Do you have any suggestions.

Thanks,

Dan

Here's the line that returned the error:
Set myRng = .Range("a2",.Cells(.Rows.Count,"A").End(xlUp))

Private Sub cboPrimary_Change()

Dim wks As Worksheet
Dim myCell As Range
Dim myRng As Range

Set wks = Worksheets("tblContacts")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))

With Me.cboSecondary
.RowSource = "" 'can't use .rowsource and .additem!
.Clear
For Each myCell In myRng.Cells
If LCase(myCell.Text) = LCase(Me.cboPrimary.Value) Then
.AddItem myCell.Offset(0, 1) '1 column to the right
End If
Next myCell
End With

End Sub

--

Dave Peterson

Hi Dave,

I changed Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
to

Set myRng = Range("contacts")

The code runs without an error and cboSecondary is populated with the
whole range not just the records with the company id selected in
cboPrimary.

"Contacts" is a dynamic named range containing columns A through I on
the worksheet tblContacts.

Column A=CompanyID
B=ContactID
C=Email
D=FirstName
E=LastName
F=SalesRepNum
G=Workphone
H=Extension
I=FaxNumber


I set up cboPrimary by typing in properties:

BoundColumn 1
ColumnCount 2
ColumnHeads True
ColumnWidths 0.5 pt;0.5 pt
Rowsource Customer (A dynamic named range on the worksheet
"tblCustomerApproved"

Thanks for your time and feedback.

Dan

I'd use:
set myrng = worksheets("sheetnamehere").range("Contacts")

Unqualified ranges could cause problems.

I don't understand enough of how you want the secondary combobox to work. Is it
the second column of the Contacts range or what?

dan dungan wrote:

Hi Dave,

I changed Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
to

Set myRng = Range("contacts")

The code runs without an error and cboSecondary is populated with the
whole range not just the records with the company id selected in
cboPrimary.

"Contacts" is a dynamic named range containing columns A through I on
the worksheet tblContacts.

Column A=CompanyID
B=ContactID
C=Email
D=FirstName
E=LastName
F=SalesRepNum
G=Workphone
H=Extension
I=FaxNumber

I set up cboPrimary by typing in properties:

BoundColumn 1
ColumnCount 2
ColumnHeads True
ColumnWidths 0.5 pt;0.5 pt
Rowsource Customer (A dynamic named range on the worksheet
"tblCustomerApproved"

Thanks for your time and feedback.

Dan

--

Dave Peterson