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]

The short answer is you can't. When you make a selection, the editbox portion of a combobox displays the column pointed to by the TextColumn property (which may be the same as the Value property). However, depending on what you are doing with the user's selection, there are a couple of ways to simulate what you're asking. In essence, you will create a column (either a third column or the one only) and populate it with a string created by concatenating the Part# and Description.


Regards,
Mike

 

[Pudsters]

That is what I originally had done, Concatenating. But it looks bad because the second column doesn't line up well when you do it that way.

 

[rmikesmith]

Try placing vbTab between the two strings. This made the 2nd "column" items line up for me. Example:

Range("A1").Text & vbTab & Range("B1").Text

Regards,
Mike

 

[Pudsters]

You lost me, where would I put that code? Do you mean go back to concatenating the two columns into one first?

 

[Pudsters]

Also, I concatenated with the formula in Excel, are you talking about doing it in vba maybe?

 

[rmikesmith]

Do you mean go back to concatenating the two columns into one first?

Yes.

Also, I concatenated with the formula in Excel, are you talking about doing it in vba maybe?

Yes.


Here is an example of how I did it:

   With Me.ComboBox1
     .ColumnCount = 3
     .ColumnHeads = False
     
     For Each OneCell In Rng
       .AddItem OneCell.Value
       .List(.ListCount - 1, 1) = OneCell.Offset(0, 1).Value
       .List(.ListCount - 1, 2) = OneCell.Value & vbTab & OneCell.Offset(0, 1).Value
     Next OneCell
   End With

Where Rng and OneCell were declared as Range and Rng was Set to Range("A1:A15") on the appropriate worksheet, for example.

Note: In the Properties Window, enter explicit width values for the first two columns and zero for the third, which will hide it. By the way, you can set the ColumnCount and ColumnHeader properties at design-time in the same fashion.

As you can see, I maintained the two original columns that you were using and added a third for display purposes only. The advantage with that setup is if your application needs to do something with the Part# of the selection it is readily available.


Mike

 

[Pudsters]

Sorry, lost again on this part:

"Where Rng and OneCell were declared as Range and Rng was Set to Range("A1:A15") on the appropriate worksheet, for example."

This was my original code:

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



I have Part#'s in Column J and Description in K (Named Range "CATEGORY" (J2-K48)

So do I concantenate into lets say Column K? If so, then how do I reference it from your code?

 

[rmikesmith]

I should have posted all my code, which may have helped. Below is better example coding. I am assuming your data is in two columns as you indicated: Part# in column J and Description in column K. Note the two alternate ways to reference the range (using my code this now emcompasses only the Part #'s)

Dim Rng As Range
Dim OneCell As Range

   Set Rng = Worksheets("Data").Range("J2:J48")
[COLOR=green]' *** Alternatively, set Rng as follows:[/color]
   Set Rng = Worksheets("Data").Range("CATEGORY")
[COLOR=green]' Where the named range CATEGORY is changed to J2:J48[/color]
   
   With Me.ComboBox1
     .ColumnCount = 3
     .ColumnHeads = False
     
     For Each OneCell In Rng
       .AddItem OneCell.Value
       .List(.ListCount - 1, 1) = OneCell.Offset(0, 1).Value
       .List(.ListCount - 1, 2) = OneCell.Value & vbTab & OneCell.Offset(0, 1).Value
     Next OneCell
   End With

Mike

 

[Pudsters]

Okay, Got it! I knew I had to reference Rng to my list, I just wasn't sure how. Works fine. The only thing is that the 2nd column still doesn't line up in a straight row because the first column is comprised of unique 3-letter codes as the part # and some letters are wider than others. I guess I could switch to Courier Font. Then it would line up but it looks ugly and wouldn't match the other lists on my form.

But I will keep it this way

 

[Pudsters]

Mike, earlier you said, "The advantage with that setup is if your application needs to do something with the Part# of the selection it is readily available."

Well guess what? That is what I want to do. I want to populate my next list box based on the 3-letter code Part # from above.

I already have some code to do it based on the code if it is in a cell but not from a selection in a list box. I assume I just need to tweak just a bit of the code to point to Combobox1, just not sure how?

 

[rmikesmith]

The only thing is that the 2nd column still doesn't line up in a straight row because the first column is comprised of unique 3-letter codes as the part # and some letters are wider than others.

I don't understand what you mean. ComboBox1 should be set to 3 columns. Column 1 should contain the Part #'s from Range("J2:J48"); Column 2 should contain the Descriptions from Range("K2:K48") and Column 3 should contain the concatenated strings generated from Cells J2 and K2 through Cells J48 and K48. Entries in the individual columns will be aligned. If anything, an entry's text may run up against the next column if the appropriate explicit ColumnWidth value is not set. Perhaps I'm missing something?

Well guess what? That is what I want to do. I want to populate my next list box based on the 3-letter code Part # from above.

Can you give more detail? I am leaving for the day but will respond tomorrow. In the interim, see what you can come up with.


Regards,
Mike

 

[Pudsters]

I combined the 3-letter codes and the descriptions (separated by a space) into one column. Named the range T_CATEGORIES. I installed this code:

Dim Rng As Range
Dim OneCell As Range

Set Rng = Worksheets("Data").Range("T_CATEGORY")

With Me.cboCategories
.ColumnCount = 3
.ColumnHeads = False

For Each OneCell In Rng
.AddItem OneCell.Value
.List(.ListCount - 1, 1) = OneCell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = OneCell.Value & vbTab & OneCell.Offset(0, 1).Value
Next OneCell

End With

I set the column widths to 250pt,250pt,0

When you scroll down the combobox, the three letters on the left, then a space, then the description. They aren't in true columns. But when you select one of them, then at the top it does change to true columns if that makes sense.

 

[rmikesmith]

Ok, I see what you've done and why that isn't working. Let me re-state the overall idea then list the specific actions to take.

The ComboBox will have 3 columns: Column 1 will contain the Part No.'s; Column 2 will contain the Part Descriptions and Column 3 will contain a concatenation of Part No. and Description. Columns 1 and 2 will be visible (when the ComboBox's dropdown is activated) while Column 3 will not. When a selection is made, the string in Column 3 will be displayed in the TextBox area of the ComboBox.

Ensure the following setup:

Data Worksheet
Part #'s in Column J -> J2:J48
Part Descriptions in Column K -> K2:K48
Named Range CATEGORY that refers to J2:J48 (the range of Part No.'s only).

cboCategories ComboBox^*
BoundColumn = 1
ColumnCount = 3
ColumnWidths = 60pt; 250pt; 0pt (adjust values for Columns 1 & 2 as needed)
TextColumn = 3
RowSource is blank
^* Use the Properties Window in the VBE to inspect/set these properties

Procedure Code

Dim Rng As Range
Dim OneCell As Range

   Set Rng = Worksheets("Data").Range("CATEGORY")
   
   With Me.cboCategories
     
     For Each OneCell In Rng
       .AddItem OneCell.Value
       .List(.ListCount - 1, 1) = OneCell.Offset(0, 1).Value
       .List(.ListCount - 1, 2) = OneCell.Value & vbTab & OneCell.Offset(0, 1).Value
     Next OneCell
   
   End With

Does the cboCategories combo appear and function properly now?


Regards,
Mike

 

[Pudsters]

Mike, you are the best! Yes, now it works perfect and it looks great. And this will come in quite handy for other projects.

Now just one more thing I need to do and my life will be complete. Hope you can help.

I need to populate a second combo box(cboDescriptions)based on the selection of the first one (but just the 3-letter code from column J. I have a database of 1600 unique items in the "Data" sheet, column C, Range named "DESCRIPTION". In colum B, named "CODE", I have the unique 3-letter code, which is the part # from Column J -"CATEGORY"

Example, if someone selects "MOT MOTORS" from first cbobox, then the second cbobox will display all motors from column C based on MOT being displayed in column B

Scott

 

[rmikesmith]

Scott,

Yes, this can be done. I'll put some example code together but it may take a while to post back.


Regards,
Mike

 

[Pudsters]

Thanks Mike, Hey are you available for Consultation and design work? I have a project coming up that I know I will need help on and would like to pay you for your services if interested.

Scott

 

[rmikesmith]

Scott,

This is a bit tricky, as I don't wish to violate any site policies, etc. Email addresses are not to be posted. However, I have a hotmail account that uses my Tek-Tips username/handle. Please contact me to discuss.

I should have some code for you later this afternoon. I'll be on the road shortly but will post back when I can.


Regards,
Mike

 

[rmikesmith]

Scott,

Here is some code to populate the cboDescriptions ComboBox based on the entry selected from the cboCategories ComboBox (specifically the Part#). All of the code shown below should reside in the code module of the Userform containing these ComboBoxes.

Private Sub PopulateDescriptions(ByVal Code As String)
Dim CodeRng As Range
Dim MatchCell As Range
Dim Descriptions() As String
Dim Count As Long
Dim FirstAddress As String

   Set CodeRng = ThisWorkbook.Worksheets("Data").Range("CODE")

   Count = 0
   With CodeRng
     Set MatchCell = .Find(What:=Code, LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, MatchByte:=False)

     If Not MatchCell Is Nothing Then
       FirstAddress = MatchCell.Address
       Count = Count + 1
       ReDim Preserve Descriptions(1 To Count)
       Descriptions(Count) = MatchCell.Offset(0, 1).Text
       Do
         Set MatchCell = .FindNext(MatchCell)
         If (Not MatchCell Is Nothing) And MatchCell.Address <> FirstAddress Then
           Count = Count + 1
           ReDim Preserve Descriptions(1 To Count)
           Descriptions(Count) = MatchCell.Offset(0, 1).Text
         End If
       Loop While Not MatchCell Is Nothing And MatchCell.Address <> FirstAddress
     End If
   End With

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

End Sub

At the top of the module add the following declaration:

Dim IgnoreEvents As Boolean

In the Userform Initialize event procedure include:

Private Sub Userform_Initialize()

   IgnoreEvents = True

   [COLOR=green]'Your existing initialization code here
   '(this is where I have the code that populates cboCategories)[/color]

   IgnoreEvents = False

End Sub

Create a Change event procedure for cboCategories:

Private Sub cboCategories_Change()

   If IgnoreEvents Then Exit Sub
   PopulateDescriptions cboCategories.Value
   
End Sub

Notes:
[ul][li]The PopulateDescriptions procedure takes as input a 3-letter code string then searches through the listing of codes, in Range("CODE"), returning each associated description.[/li]
[li]When the user selects a Part# from cboCategories, its Change event handler code executes and calls PopulateDescriptions with the selected Value (the Part#/3-Letter code).[/li]
[li]The use of the Boolean IgnoreEvents prevents the event handler code from running when the Change event fires while cboCategories is being populated.[/li][/ul]


Regards,
Mike

 

[Pudsters]

Mike, I think I did what you said but get a 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

Below is the entire code window:

Dim IgnoreEvents As Boolean


Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub cboDescriptions_Change()

End Sub

Private Sub cboCategories_Change()

If IgnoreEvents Then Exit Sub
PopulateDescriptions cboCategories.Value

End Sub


Private Sub cmdAdd_Click()
Dim TotalLines As Long

ActiveWorkbook.Sheets("Invoice").Activate

TotalLines = Application.WorksheetFunction.CountA(Range("A1:A10"))
If TotalLines = 9 Then
MsgBox "Sorry, Invoice is Full. Must start another invoice to add more items."
Else
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboDescriptions.Value
ActiveCell.Offset(0, 2) = txtSilverstar.Value
ActiveCell.Offset(0, 3) = txtRegular.Value
ActiveCell.Offset(0, 4) = txtAfterhours.Value
ActiveCell.Offset(0, 5) = txtQuantity.Value
Range("A1").Select
End If
End Sub

Private Sub fraLevel_Click()

End Sub


Private Sub ListBox1_Click()

End Sub

Private Sub optZone4_Click()

End Sub
Private Sub optZone3_Click()

End Sub

Private Sub optZone2_Click()

End Sub

Private Sub optZone1_Click()

End Sub

Private Sub PopulateDescriptions(ByVal Code As String)
Dim CodeRng As Range
Dim MatchCell As Range
Dim Descriptions() As String
Dim Count As Long
Dim FirstAddress As String

Set CodeRng = ThisWorkbook.Worksheets("Data").Range("CODE")

Count = 0
With CodeRng
Set MatchCell = .Find(What:=Code, LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, MatchByte:=False)

If Not MatchCell Is Nothing Then
FirstAddress = MatchCell.Address
Count = Count + 1
ReDim Preserve Descriptions(1 To Count)
Descriptions(Count) = MatchCell.Offset(0, 1).Text
Do
Set MatchCell = .FindNext(MatchCell)
If (Not MatchCell Is Nothing) And MatchCell.Address <> FirstAddress Then
Count = Count + 1
ReDim Preserve Descriptions(1 To Count)
Descriptions(Count) = MatchCell.Offset(0, 1).Text
End If
Loop While Not MatchCell Is Nothing And MatchCell.Address <> FirstAddress
End If
End With

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


End Sub


Private Sub txtAfterhours_Change()

End Sub

Private Sub Userform_Initialize()

IgnoreEvents = True


txtRegular.Value = "$80.00"
txtSilverstar.Value = "$80.00"
txtAfterhours.Value = "$120.00"
txtQuantity.Value = "1"
txtNotes.Value = "There are no notes associated with this task."


' The following Concatenates the Task Code and Task Category List
' Making 3 Columns in Categories ComboBox, 3rd column is hidden
' except when selected. Notice all settings in Properties box.


Dim Rng As Range
Dim OneCell As Range

Set Rng = Worksheets("Data").Range("CATEGORY")

With Me.cboCategories

For Each OneCell In Rng
.AddItem OneCell.Value
.List(.ListCount - 1, 1) = OneCell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = OneCell.Value & vbTab & OneCell.Offset(0, 1).Value
Next OneCell

End With



With cboDescriptions

End With



optZone1 = True

IgnoreEvents = False


End Sub