ListFillRange challenge

[Guest]

On Sheet1, I have users select a project name in cell A1 from a list created
using a data validation range. In cell B1, I have created a combo box using
the Control Toolbox toolbar. The purpose of the combo box is to look up the
project name selected in A1 and then display 2 columns of information (CODE
#, DESCRIPTION) for the user to choose from. The combo box will get its
values from Sheet2 (using the ListFillRange property), where I have the
following information listed:

PROJECTS STATUS CODE # DESCRIPTION
Project A A 10001 Plan 1
Project A A 10002 Plan 2
Project A A 10003 Plan 3
Project A A 10004 Plan 4
Project B A 20001 Plan 5
Project B A 20002 Plan 6
Project B A 20003 Plan 7
Project C A 30001 Plan 8
Project C A 30002 Plan 9
Project C A 30003 Plan 10
Project C A 30004 Plan 11

My problem is writing code for the click event of the combo box that looks
up the value of Sheet1!A1, finds a match for it in the PROJECTS column on
Sheet2, and then displays both the CODE# and DESCRIPTION options for that
project in the combo box for the user to choose from. For example, if Project
B is selected, I want the combo box choices to be
CODE # DESCRIPTION
20001 Plan 5
20002 Plan 6
20003 Plan 7

I know how to set the properties of the combo box; I just need help with the
code.
Thanks!

Steve C

 

[Tom Ogilvy]

The click event fires when a selection is made. You should probably
populate the combobox from the Change event of Sheet1 for the cell A1.
Don't use the listfill range property for the combobox - it won't allow you
do to what you want.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim res as Variant, rng as Range
Dim rng1 as Range, i as Long
If Target.Address = "$A$1" then
Combobox1.ColumnsCount = 2
Combobox1.ListfillRange = ""
Combobox1.Clear
With worksheets("Sheet2")
set rng = .Range(.Cells(2,1),.Cells(2,1)).End(xldown))
End with
res = Application.Match(Target,rng,0)
set rng1 = rng(res)
i = 0
do while rng(i) = Target
Combobox1.AddItem rng(i).offset(0,2).Value
Combobox1.List(combobox1.ListCount-1,1) = _
rng(i).offset(0,2).Value
Loop
End Sub

 

[Guest]

Tom,

When testing your code (which I placed in the Sheet1 module in the VB
Editor), I'm getting a Run-time error 13: Type mismatch for the following
line of code:

set rng1 = rng(res)

Also, does the location of the information on Sheet2 matter? There is a
potential for this information to change (it's linked to another workbook
that is updated with new project names, Code #'s and Descriptions as they are
created). Thanks again for all your help!

 

[Tom Ogilvy]

You said the value of Target is obtained from a data validation dropdown, so
I assumed it would be legitimate value from the list where the data is
locate. The type mismatch would indicate the Match has returned an #N/A
error meaning the item in A1 did not match. I didn't check for a blank
value in A1 so possibly that is another problem.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim res As Variant, rng As Range
Dim rng1 As Range, i As Long
If Target.Address = "$A$1" Then
If Target.Value <> "" Then
combobox1.ColumnsCount = 2
combobox1.ListFillRange = ""
combobox1.Clear
With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Set rng1 = rng(res)
i = 0
Do While rng(i) = Target
combobox1.AddItem rng(i).Offset(0, 2).Value
combobox1.List(combobox1.ListCount - 1, 1) = _
rng(i).Offset(0, 2).Value
Loop
Else
MsgBox "Value in A1 is invalid"
End If
End If
End If
End Sub

You need to know where your data is.

 

[Guest]

Tom,

Your code has helped me immensely. One question regarding this line:

If Target.Address = "$A$1" Then

Can the target be a named range? I've tried the following, but it doesn't
seem to recognize anything (where Project1 = $A$1):

If Target.Address = "Project1" Then
If Target.Address = Project1 Then
If Target.Address = Range("Project1") Then

Thanks!

 

[Dave Peterson]

Is project1 a single cell?
If Target.Address = Range("Project1").address Then

 

[Guest]

Yes, it is a single cell. That did the job! Thanks, Dave.