Solved Select column value in a listbox (1 Viewer)

[craigachan]

I have a listbox that is populated with a value list. The listbox has 4 columns. Each value in the listbox is its own unique value. Is it possible to click and return a value in a column or row. Example:

5 Tooth Wave 87
tree YY GRF 760

How would I click select a specific value, such as 'Wave'. and actually get the 'wave' value. Or click 'tree' and return the 'tree' value.

Is this possible? Thanks for any input.

 

[oleronesoftwares]

Listbox.column(0) // retrieves 1st column
listbox.column(1) // retrieves 2nd column
listbox.column(2) // retrieves 3rd column
listbox.column(3) // retrieves 4th column

 

[bastanu]

You are not giving us enough info on the purpose\intended usage but for a multiselect list box you can use .Column(Index, Row) to get those values. See here for more info:

ListBox.ItemsSelected property (Access)

Office VBA reference topic

docs.microsoft.com

ListBox.Column property (Access)

Office VBA reference topic

docs.microsoft.com

Cheers,

 

[craigachan]

Thank you for your responses. What I'm getting as is if the user clicks 'Wave'. How does the code know when the user is clicking row 1 and column(3)? I don't understand how the code knows which column the user is clicking? Does that make any sense? What if the user clicks something in column(0). How does the code know?

 

[bastanu]

Unfortunately list boxes (or combo boxes) don't work that way, it does not matter where in the list (from the left to the right edge) you click, the end result is that you select (or unselect) the row. When the row is selected the list box's value (for single select mode) becomes that of the bound column of its row source and to access the other columns you use the syntax provided in post #2. For multiselect you need to employ the technique I provided in my previous post.

Cheers,

 

[GPGeorge]

Thank you for your responses. What I'm getting as is if the user clicks 'Wave'. How does the code know when the user is clicking row 1 and column(3)? I don't understand how the code knows which column the user is clicking? Does that make any sense? What if the user clicks something in column(0). How does the code know?

Yes, Vlad is right. You CAN'T click to select any individual column in a list box because it is a Row-wise display of values. I.e. if there are four fields in the rowsource bound to the list box, selecting a row means selecting all four fields at once. This of it as a package, rather than individual item. You've seen shrink-wrapped items in the store where the individual items inside are all marked "Not for Individual Sale", right? That's the same concept. The columns are part of the package when you select them.

What you want to do is select a row and retrieve any one of the columns at random. But because it's not marked for individual use, that column remains indistinguishable in the context. No can do.

The other thing you need to know is that ONE, and ONLY ONE, of the columns in the listbox is actually the BOUND column, i.e. the column which identifies the row. Usually that is the first column on the left, or Column(0) because columns are indexed left to right starting with 0, i.e. the index is zero based. You can get the values from other columns by referring to their indexes, i.e. Column(1) is the second column from the left, and so on. When you click on a row, what you get is the value in the Bound column, i.e. Column(0)

Therefore, if you want a user to select both a row AND a column, the list box is not the appropriate tool. Perhaps a Cascading Combo Box method would be a good alternative. Do a search for that term and see if that would work for this requirement.

 

[craigachan]

Thanks bastanu. I was hoping there might be a great way to do this. But I guess my only way is to create a single column listbox and then when the user clicks it will be column(0). Thanks for all of the input.

Thanks GPGeorge also. I'm going to use a group option buttons at the top of the form to filter the listbox with each option click of now my single column listbox. I don't want the user to have to click to open the cbo box.

 

[MajP]

See solution. Does exactly that.

 

[MajP]

FYI. This class turns any listbox into one where you can get the value of the selected column.
Simply declare a variable. Then instantiate it. It has a property to return the column and value of the item selected.

Private CLB As New ColumnListBox


Private Sub Form_Load()
  CLB.Init Me.List0
End Sub
Private Sub List0_Click()
  MsgBox CLB.ClickedColumn
  MsgBox CLB.ClickedColumnValue
End Sub

 

[MajP]

For those interested the code is pretty basic.
1. Get the column widths.
2. On the mouse down even get the X position. Figure out which column that is
3. Once you figure out which column then return the value of that column.

Private WithEvents mListBox As Access.ListBox
Private mColWidths As New Collection
Private mClickedColumn As Integer
Private mClickedValue As String

Public Sub Init(TheListBox As ListBox)
  Set mListBox = TheListBox
  mListBox.OnMouseDown = "[Event Procedure]"
  loadColumnWidths
End Sub
Private Sub loadColumnWidths()
  Dim aColWidths() As String
  Dim colWidth As Variant
  aColWidths = Split(mListBox.ColumnWidths, ";")
  For Each colWidth In aColWidths
     mColWidths.Add (CLng(colWidth))
  Next colWidth
End Sub
Private Function GetClickedColumn(X As Single)
  Dim TotalWidth As Single
  Dim itm As Variant
  Dim I As Integer
  For Each itm In mColWidths
    TotalWidth = TotalWidth + itm
    If X < TotalWidth Then
      GetClickedColumn = I
      Exit Function
    End If
    I = I + 1
  Next itm
End Function
Private Sub mListBox_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  mClickedColumn = GetClickedColumn(X)
End Sub
Public Property Get ClickedColumn() As Integer
  ClickedColumn = mClickedColumn
End Property
Public Property Get ClickedColumnValue() As Variant
   ClickedColumnValue = mListBox.Column(Me.ClickedColumn)
End Property

 

[theDBguy]

Hi. Not in front of a computer now, but I thought the Column() property retrieves both the column and row value?

Listbox.Column(col, row)

 

[craigachan]

Thank you for your response MajP. I think your solution is exactly what I'm looking for.

I'm trying to apply your code to my application but am having an issue. At first I added all of your code with the necessary changes to apply to my listbox. But I got:

"The expression On Click event that you entered as the event property setting produced the following error: User-defined type not defined"

So I tried tried adding your code slowly starting with the private declare: Private CLB as New ColumnListbox

This line alone caused the above error. Even after I started adding your code sub by sub, it did not get rid of the error. I'm not sure what to do at this time because I guess I don't quite understand the function. Most likely I'm not making the necessary changes to your code. Any insight on this is appreciated.

By the way, I have all of the code in the current form.

 

[moke123]

By the way, I have all of the code in the current form.

MajP's code is a custom class. You need to put the "ColumnListBox" code into a class module, not a standard module or form modules.

In your form you should copy what is in MajP's form

Private CLB As New ColumnListBox

Private Sub Form_Load()
    CLB.Init Me.List0
End Sub
Private Sub List0_Click()
    MsgBox CLB.ClickedColumn
    MsgBox CLB.ClickedColumnValue
End Sub

The only change you need to make is the name of your listbox. - CLB.Init Me.YourListBoxName

CLB.ClickedColumnValue will return the value in the column selected.

x = CLB.ClickedColumnValue

 

[MajP]

Although this should work fine, I never found an actual need to do this and not sure if it is a user friendly of a GUI. Out of curiosity why would you need to return different values from the listbox? My first guess is the database is not normalized, because normally you are saving a primary key from the listbox. Also there may be a nicer user interface to accomplish this.

 

[moke123]

Out of curiosity why would you need to return different values from the listbox?

I was wondering that too. The only scenario I could think of was something like this, however I dont really have a use for it. Nice little class though.

 

[craigachan]

Thank you for all of your help. I'm learning a lot as I go along. I'm now having a problem with a different error. My listbox is me.lstDx. So I've modified the code to "CLB.Init Me.lstDx" in the Form_Load(). But now I'm getting a runtime "424-Object required" that error right at the same line. I'm totally lost. I've put the rest of MajP's code in their respective subs on the form. And also made a new class module for the 'Private CLB As New ColumnListBox' and save the class module as ColumnListBox (I hope this is okay).

To answer everyone's questions. My base table is a single field table populated with with Diagnositic Codes. The single field is the primary and unique. From this I created a value list for my listbox called .lstDx. so it looks like this. I was trying to have the for short and wide form rather then a tall list that the user would have to scroll down for. The other reason is to challenge myself to learn something new.

 

[MajP]

any chance you can post what you have?

 

[Pat Hartman]

Or, you could use the listbox as it was intended to be used and not need the class at all. The filters above shorten the list so there shouldn't be a lot of scrolling. Plus type ahead rapidly narrows the list.

Although, this is actually a rational use of the class that MajP created for someone else. The original post had something to do with music and the reason for the multiple column ability was just bizarre.

 

[craigachan]

Class Module - ColumnListBox

Option Compare Database

Public CLB As New ColumnListBox

Form CodesDx

Option Compare Database

Private WithEvents mListBox As Access.ListBox
Private mColWidths As New Collection
Private mClickedColumn As Integer
Private mClickedValue As String

Private Sub cmdAddCode_Click()
    DoCmd.OpenTable "tblCodesDx"
End Sub

Private Sub cmdDxFilters_Click()
    DoCmd.OpenTable "tblCodesDxFilter"
End Sub

Private Sub cmdRefresh_Click()
    Call RefreshForm                'Private
End Sub

Private Sub Form_Load()
    '====Make a list for lstDx of all diagnostics codes
    Dim rs As DAO.Recordset, strList As String, Y As Long, rsD As DAO.Recordset
    Me.Move 6000, 1000, 9000, 6000
    CLB.Init Me.lstDx
    
    Me.aa.SetFocus
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblCodesDx ORDER BY CodeDx")
    
    Do Until rs.EOF
        If strList = "" Then
            strList = rs!CodeDx
        Else
            strList = strList & ";" & rs!CodeDx
        End If
    
        rs.MoveNext
    Loop
    Me.lstDx.ColumnCount = 4
    Me.lstDx.ColumnWidths = "1in,1in,1in,1in"
    Me.lstDx.RowSource = strList
    rs.Close
    
    Me.frlbl1.Caption = "All"
    Me.frlbl1.Visible = True
    
    '===Setup DxFilter Option Group
    Set rsD = CurrentDb.OpenRecordset("SELECT * FROM tblCodesDxFilter ORDER BY DxFilter")
    '===Hide all filter options
    For Y = 2 To 9
        Me("frlbl" & Y).Visible = False
        Me("ck" & Y).Visible = False
    Next Y
    
    '===UnHide if allowed
    Y = 2
    Do Until rsD.EOF
        Me("frlbl" & Y).Visible = True
        Me("frlbl" & Y).Caption = rsD!dxfilter
        Me("ck" & Y).Visible = True
        rsD.MoveNext
        Y = Y + 1
    Loop
    rsD.Close
    Set rs = Nothing
    Set rsD = Nothing
End Sub

Private Sub RefreshForm()
    On Error GoTo RefreshErr
    'Dim rsF As DAO.Recordset, strFilter As String
    Me.frDxFilters = 1
    Call Form_Load      'Private
    
RefreshExit:
    Exit Sub
    
RefreshErr:
    MsgBox "Forms!CodesDx: " & Err.Number & " - " & Err.Description
    Resume RefreshExit

End Sub

Private Sub lstDx_Click()
    MsgBox CLB.ClickedColumn
    MsgBox CLB.ClickedColumnValue
End Sub

Private Sub frDxFilters_Click()
    Dim rsListbox As String, strFilter As String, msql As String, rs As DAO.Recordset
    'strFilter = Me.frDxFilters
    strFilter = Me("frlbl" & Me.frDxFilters).Caption
    If Me.frDxFilters = 1 Then
        msql = "SELECT * FROM tblCodesDx ORDER BY CodeDx ASC;"
    Else
        msql = "SELECT * FROM tblCodesDx WHERE CodeDx Like '" & strFilter & "*' ORDER BY CodeDx ASC;"
    End If
    Set rs = CurrentDb.OpenRecordset(msql)
    Do Until rs.EOF
        If rsListbox = "" Then
            rsListbox = rs!CodeDx
        Else
            rsListbox = rsListbox & ";" & rs!CodeDx
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Me.lstDx.RowSource = rsListbox
    
End Sub
Private Sub Init(TheListBox As ListBox)
  Set mListBox = TheListBox
  mListBox.OnMouseDown = "[Event Procedure]"
  loadColumnWidths
End Sub
Private Sub loadColumnWidths()
  Dim aColWidths() As String
  Dim colWidth As Variant
  aColWidths = Split(mListBox.ColumnWidths, ";")
  For Each colWidth In aColWidths
     mColWidths.Add (CLng(colWidth))
  Next colWidth
End Sub
Private Function GetClickedColumn(X As Single)

  Dim TotalWidth As Single
  Dim itm As Variant
  Dim I As Integer
  For Each itm In mColWidths
    TotalWidth = TotalWidth + itm
    If X < TotalWidth Then
      GetClickedColumn = I
      Exit Function
    End If
    I = I + 1
  Next itm
End Function
Private Sub mListBox_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  mClickedColumn = GetClickedColumn(X)
End Sub
Public Property Get ClickedColumn() As Integer
 ClickedColumn = mClickedColumn
End Property
Public Property Get ClickedColumnValue() As Variant
   ClickedColumnValue = mListBox.Column(Me.ClickedColumn)
End Property

Thank you and I appreciate all of your help.

 

[moke123]

Maybe something more along the lines of a calendar type layout would be easier.