2-Column ListBox, 1 Column shows when selected 1

[Pudsters]

I finally figured out how to populate a list box with two columns, Yeah! Even adjusted the widths. Looks Great.
Column A is a Part#, Column B is Part Description.
But when I make a selection from the list, only the left column (part#) shows up. How Do I get Column B to also show up?


With Me.cboCategories
.ColumnCount = 2
.RowSource _
= Worksheets("Data").Range("CATEGORY").Address(external:=True)
.ColumnHeads = False
End With

 

[rmikesmith]

What error is occurring?


Mike

 

[Pudsters]

As soon as I select a catagory from the Category box, I get a unspecified runtime error. The .Clear is highlighted in yellow from the bottom of your code.

With cboDescriptions
.Clear
If Count > 0 Then
.List = Descriptions
End If
End With

End Sub

 

[rmikesmith]

Check the RowSource property of the cboDescriptions ComboBox. I suspect you have this set. If so, clear it.


Regards,
Mike

 

[Pudsters]

You were right, I cleared the RowSource property. The Error went away, but now there is NOTHING showing in the cboDescription cboBox.

 

[rmikesmith]

there is NOTHING showing in the cboDescription cboBox.

Can you elaborate?

Do you mean after making a selection in cboCategories there is nothing listed in cboDescriptions when its dropdown is activated?


Mike

 

[Pudsters]

Sorry Mike, there is nothing in the box in any state, whether a category is selected or not, Nothing.

 

[rmikesmith]

I would set a breakpoint inside procedure PopulateDescriptions to see what is happening. Start with

   With cboDescriptions [COLOR=red]<--- Set breakpoint here[/color]
     .Clear
     If Count > 0 Then
       .List = Descriptions
     End If
   End With

1. Run the Userform
2. Select a Part# from the cboCategories ComboBox

When the code execution pauses at the breakpoint, inspect the value of Count to see if it's >0. If not, clear the breakpoint and set another at the top of the procedure. Repeat 1 and 2 above then single-step through the code and try to see what is going on.

Note: The codes entered on the Data worksheet in Column B must match those in Column J for the routine to work properly.

Regards,
Mike

 

[Pudsters]

Mike, the codes are all the same. All 3-letter unique (in Caps)

I can't get anything to happen. I did find this line highlight to yellow when I put a break at it.


Private Sub cboCategories_Change()

If IgnoreEvents Then Exit Sub
PopulateDescriptions cboCategories.Value

End Sub

 

[rmikesmith]

Just to be sure I understand the data layout. Please confirm or correct the following:

The 3-Letter codes in Column J are unique (i.e., not duplicated). Yes?

Column C contains 1600 unique items, which fall into some number of categories less than 1600. The categories are represented by 3-Letter codes in Column B. Column B does contain duplicates.

Every 3-Letter code in Column B also exists in Column J.


Here is the data layout I used to set up the routines I supplied. The 3-Letter codes are made up except for the MOT code that you mentioned:

Col B Col C Col J Col K
ABC ABC Item 1 ABC Description 1
DEF DEF Item 1 DEF Description 2
MOT Motor 1 GHI Description 3
GHI GHI Item 1 JKL Description 4
XYZ XYZ Item 1 MNO Description 5
ABC ABC Item 2 PQR Description 6
MOT Motor 2 STU Description 7
MOT Motor 3 VWX Description 8
JKL JKL Item 1 YZ1 Description 9
QRS QRS Item 1 MOT Motors
QRS QRS Item 2
MOT Motor 4
JKL JKL Item 2
MOT Motor 5
ABC ABC Item 3
MOT Motor 6
MOT Motor 7
MOT Motor 8
TUV TUV Item 1
DEF DEF Item 2


Regards,
Mike

 

[Pudsters]

Mike, everything you just wrote is EXACTLY CORRECT and is exactly how it's layed out. By the way, here is an example of data layout

(A) (B) (C)
1 DIA DIA-1000-0000 DIAGNOSTIC FEE/TRIP CHARGE 1ST ZONE
2 DIA DIA-2000-0000 DIAGNOSTIC FEE/TRIP CHARGE 2ND ZONE
3 ACA ACA-A013-0015 REPLACE HONEYWELL T 87F THERMOSTAT
4 ACA ACA-A015-0030 REPLACE HONEYWELL Q 539A SUBASE

(J) (k)
DIA DIAGNOSTIC FEE/TRIP
ACA ATMOSPHERIC CONTROLS
ACY AIR CONDITIONING YORK


But nothing is in or shows up in the second combo box.

 

[Pudsters]

btw, there are about 46 unique categories

 

[rmikesmith]

There must be something in the way the data is stored or other non-obvious discrepancy. Difficult to diagnose remotely. I again suggest single-stepping through your code so you can inspect variables, etc. In particular, see what is happening as PopulateDescriptions runs. What is the value of the parameter Code? Look for extra spaces padding either end of the text (should be 3 characters long exactly). If you know the value of Code exists in Column B, check the value of MatchCell in this line

Set MatchCell = .Find(What:=sCode, LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, MatchByte:=False)

If it is Nothing then there is a discrepancy between the representations in Column B and Column J.

Post back with your findings.

Mike

 

[Pudsters]

Mike, I can't find anything, check your email.

Scott