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

Hi Dave,

Pardon me for not explaining enough.

My goal is to present the service rep a list of company names in a
dropdown menu. When she selects the company name, I want to present
all the contacts available for that company.

Then, when she chooses the contact I want to populate cells on a
worksheet with the Company Name, Contact first name, last name email
address, work phone, and fax number. I'm thinking of putting each of
those in a textbox and then use the textbox to populate the cell.


The worksheet tblCustomerApproved has the following fields in a named
range called "Customers"

Column A=CompanyID
B=CompanyName

the worksheet tblContacts has the following fields in a named range
called "Contacts".

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

I hope that makes more sense.

Thanks,

Dan

So you choose one of the companyid/companyName choices from the first combobox.
Then you display another combobox/listbox of the contacts for that company,
right?

Some things I need verified (so I won't have to guess wrong)...

Each company is defined by the companyid field.
Each companyid has at least 1 contact, but may have several contacts.

After the user chooses the company, what gets populated?

Is it a listbox with all the possible contacts so the user can choose which one
they want to use?

Or is it a worksheet (based on the activecell???) gets all the contact info for
each of the contacts that match the companyid?

If you show a listbox, then what gets shown in that listbox--how many columns?

Can that secondary listbox have multiple selected items--or just one?

I'm sure there's more questions, but these seem the most important.

dan dungan wrote:

Hi Dave,

Pardon me for not explaining enough.

My goal is to present the service rep a list of company names in a
dropdown menu. When she selects the company name, I want to present
all the contacts available for that company.

Then, when she chooses the contact I want to populate cells on a
worksheet with the Company Name, Contact first name, last name email
address, work phone, and fax number. I'm thinking of putting each of
those in a textbox and then use the textbox to populate the cell.

The worksheet tblCustomerApproved has the following fields in a named
range called "Customers"

Column A=CompanyID
B=CompanyName

the worksheet tblContacts has the following fields in a named range
called "Contacts".

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

I hope that makes more sense.

Thanks,

Dan

--

Dave Peterson

Answers below inline:

So you choose one of the companyid/companyName choices from the first combobox.
Then you display another combobox/listbox of the contacts for that company,
right?

Right!

Each company is defined by the companyid field. *

Yes.

Each companyid has at least 1 contact, but may have several contacts. Yes


After the user chooses the company, what gets populated?

Is it a listbox with all the possible contacts. . .

I was trying to use a combobox, but I don't really understand when to
use a combobox or when to use lisbox. All I need to show is the first
and last name so the user can choose the person that will eventually
get an email.

Or is it a worksheet (based on the activecell???) gets all the contact info for
each of the contacts that match the companyid?

The worksheet is a form. So I just realized I only need the CompanyId
and ContactID to be populated in the worksheet.

A2=Company Id
B2=ContactID

Then I import this and the quote data to Access and print the quote
from a report. Right now all I have is the company name. But I need to
add the contact so we can send the emails from access and so the quote
will have the contact's name, phone number and email address.

If you show a listbox, then what gets shown in that listbox--how many columns?

The user needs to see the company name and the contact first and last
name so they can choose the company and contact they are going to
quote.

Can that secondary listbox have multiple selected items--or just one?

Just one

Thanks again for your help.

Dan

I dumped all 9 columns of the selected contact. But that doesn't mean you need
to. You could just use what you want.

If I want to display more than one column, I like to use a listbox. You can use
a combobox and the user will be able to see all the columns when they show the
list. But as soon as they choose an item, only one column will appear in the
combobox (as you've seen, I bet).

As a user, I like to see the info--and I think it's easier to see more info in a
listbox (without having to expand the dropdown (like in a combobox).

Anyway...

I created a small userform--two listboxes, a label and two commandbuttons.

I like to let the code do all the properties (except for the default stuff).
And I let the code determine the ranges--instead of using the dynamic name that
you defined.

That's a minor difference--you can change this later.

Here's the code that was behind the userform:

Option Explicit
Dim CustRng As Range
Dim ContRng As Range

Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()

Dim wks As Worksheet
Dim DestRow As Long
Dim cCtr As Long

Set wks = Worksheets("Sheet1")

If Me.ListBox2.ListIndex < 0 Then
'nothing chosen!
Else
Me.Label1.Caption = "" 'clear that label
End If

DestRow = 7 'however you determine what goes where

'all 9 columns!
For cCtr = 1 To ContRng.Columns.Count
With Me.ListBox2
wks.Cells(DestRow, cCtr).Value = .List(.ListIndex, cCtr - 1)
End With
Next cCtr

End Sub
Private Sub ListBox1_Change()

Dim myCell As Range
Dim cCtr As Long

'this shouldn't happen!
If Me.ListBox1.ListIndex < 1 Then
'nothing selected
Beep
Exit Sub
End If

Me.Label1.Caption = "Please select a contact"

Me.ListBox2.Clear

With Me.ListBox1
For Each myCell In ContRng.Columns(1).Cells
If LCase(myCell.Value) = LCase(.List(.ListIndex, 0)) Then
'it's a match
'add all 9 columns to
'add column D (firstname name)
With Me.ListBox2
.AddItem myCell.Value
'add next 8 columns
For cCtr = 2 To ContRng.Columns.Count
.List(.ListCount - 1, cCtr - 1) _
= myCell.Offset(0, cCtr - 1).Value
Next cCtr
End With
End If
Next myCell
End With

End Sub
Private Sub UserForm_Initialize()

With Worksheets("Customers")
'Set CustRng = .range("Customerrangetablehere") 'your name
'or let the code decide
Set CustRng = .Range("A2:B" & .Cells(.Rows.Count, "a").End(xlUp).Row)
End With

With Worksheets("Contacts")
Set ContRng = .Range("A2:i" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectSingle
.ColumnCount = CustRng.Columns.Count
.ColumnWidths = "30;30"
.ListStyle = fmListStyleOption
.RowSource = "" 'let the code do the work
'put the list in the listbox
.List = CustRng.Value 'all at once
End With

With Me.ListBox2
.MultiSelect = fmMultiSelectSingle
.ColumnCount = ContRng.Columns.Count
.ColumnWidths = "0;0;0;35;35;0;0;0;0"
.ListStyle = fmListStyleOption
.RowSource = ""
End With

With Me.CommandButton1
.Caption = "Cancel"
.Enabled = True
.Cancel = True
End With

With Me.CommandButton2
.Enabled = True
.Caption = "Process"
End With

Me.Label1.Caption = "Please select a company"

End Sub



dan dungan wrote:

Answers below inline:

So you choose one of the companyid/companyName choices from the first combobox.
Then you display another combobox/listbox of the contacts for that company,
right?

Right!

Each company is defined by the companyid field.

Yes.

Each companyid has at least 1 contact, but may have several contacts. Yes

After the user chooses the company, what gets populated?

Is it a listbox with all the possible contacts. . .

I was trying to use a combobox, but I don't really understand when to
use a combobox or when to use lisbox. All I need to show is the first
and last name so the user can choose the person that will eventually
get an email.

Or is it a worksheet (based on the activecell???) gets all the contact info for
each of the contacts that match the companyid?

The worksheet is a form. So I just realized I only need the CompanyId
and ContactID to be populated in the worksheet.

A2=Company Id
B2=ContactID

Then I import this and the quote data to Access and print the quote
from a report. Right now all I have is the company name. But I need to
add the contact so we can send the emails from access and so the quote
will have the contact's name, phone number and email address.

If you show a listbox, then what gets shown in that listbox--how many columns?

The user needs to see the company name and the contact first and last
name so they can choose the company and contact they are going to
quote.

Can that secondary listbox have multiple selected items--or just one?

Just one

Thanks again for your help.

Dan

--

Dave Peterson

Thanks Dave.

I've installed the code you wrote, and it looks great.

Now, I need to study it to see how it works.

Thanks for your time and great explanations.

You are a real asset to the forum.

Thanks again.

Dan

There are lots of different ways to accomplish the same thing. But storing the
data in hidden columns in the listbox seems like a reasonable approach.

dan dungan wrote:

Thanks Dave.

I've installed the code you wrote, and it looks great.

Now, I need to study it to see how it works.

Thanks for your time and great explanations.

You are a real asset to the forum.

Thanks again.

Dan

--

Dave Peterson