Showing a checkbox in a listbox or combo box (1 Viewer)

[CJ_London]

I needed a means to quickly view boolean values in a combo or listbox (I didn't want to use a subform). However since these lists are text, checkboxes cannot be shown, instead you get true/false.

However with a small modification to the recordsource, you can get checkboxes

Better still, with a couple of lines of code you can make the listbox updateable (works for a combo as well, but the dropdown functionality gets in the way). Not so easy to demonstrate with images, please see the attached db

the rowsource to the first example is simply

SELECT PK, fName, fSelected FROM Table1

for the second example we make use of the chrW and iif functions

SELECT PK, fName, chrw( iif(fSelected,9745,9949)) AS Selected FROM Table1

the same rowsource for the third example, but code in the listbox afterupdate event to manage updates

Private Sub UDListbox_AfterUpdate()
Const sqlStr = "UPDATE Table1 SET fSelected = Not [fSelected] WHERE PK="

     With UDListbox
  
            CurrentDb.Execute sqlStr & .ItemData(.ListIndex - .ColumnHeads)
            .Requery

    End With
 
End Sub

as an alternate to the checkbox, just use a tick and leave the false's blank

Note that for booleans, 0 is false, any other number is true (not just -1). so you might create your boolean with a comparison or using an exists subquery (although these would not be updateable). For example in relation to showing whether an invoice has been created you could use an exists subquery to display a tick or cross rather than displaying the invoice number or date. Or indicate that membership has been paid (or not)

edit: small change to the last listbox to make it updateable

 

[arnelgp]

do you have sample of Row Source Type = Value List?

 

[CJ_London]

yes

The list rowsource might be (note this example does not have headings)

List0.RowSource = "name1," & ChrW(9745) & ",name2," & ChrW(9949)

and the listbox afterupdate event

Private Sub List0_AfterUpdate()
Dim s() As String
Dim r As String
Dim i As Integer

     With List0
            
        r = .RowSource
        s = Split(r, ",")
       i = ((.ListIndex - .ColumnHeads) * .ColumnCount) + 1 'modify the 1 to relate to the column being updated
        If AscW(s(i)) = 9949 Then s(i) = ChrW(9745) Else s(i) = ChrW(9949)
        r = ""
        For i = 0 To UBound(s)
            r = r & "," & s(i)
        Next i
        .RowSource = Mid(r, 2)

    End With

End Sub

would need additional code of course to update a variable or property if required - I'm using the latter to control how a treeview form behaves when opening/closing nodes and allows the user to save the current view/settings for next time if required.

Another variation would be to have multiple yes/no columns. You would need to determine which column was being clicked. I would get the X position of the cursor - either using API's or perhaps use the mouse down or up events to populate a form variable

Could use a subform of course - gives you more flexibility for formatting - and for a value list, populate an ADO disconnected recordset instead. But this example is about using a listbox.

 

[arnelgp]

Microsoft Forms 2.0 Listbox has same "option"

 

[CJ_London]

your point being?

 

[arnelgp]

it is but a re-invention.

 

[CJ_London]

perhaps you would like to show the code you used to populate and manage the forms 2 listbox?

Otherwise, if all you are prepared to do is offer snide comments, then don't bother.

 

[MajP]

perhaps you would like to show the code you used to populate and manage the forms 2 listbox?

The MSFORMS listbox has two properties that you have to modify. If doing this in a MSFORMS you can get these properties in the designer. I do not think you can get these in an access form without code. I try to avoid ActiveX whenever possible because of portability issues, but the MSFORMS controls are pretty stable compared to others. This lisbox is also aesthetically nice IMO.

These are:
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti


In the below I added three items, but you can also bind the MSFORMS controls

Private Sub Form_Load()
  Dim lst As MSForms.ListBox
  Set lst = Me.ListBox7.Object
  With lst
    .ListStyle = fmListStyleOption
    .MultiSelect = fmMultiSelectMulti
    .AddItem "Dog"
    .AddItem "cat"
    .AddItem "Mouse"
  End With
End Sub

 

[MajP]

I thought there was a rowsource property, but I think it is takes an excel range

VBA Listbox - A Complete Guide - Excel Macro Mastery

The VBA ListBox is a very useful control. If you are creating any kind of UserForm application you will most likely use it. In this post, I’m going to show you everything you need to know about the VBA ListBox so you can avoid the common pitfalls and get up and running quickly and easily. […]

excelmacromastery.com

Also I do not think you can add multi collumns of data in the add item method like in access. So here is how I did your multi column from a table.

Private Sub Form_Load()
  Dim lst As MSForms.ListBox
  Dim rs As DAO.Recordset
  Dim i As Integer
 
  Set rs = CurrentDb.OpenRecordset("select * from table1 order by Fname")
  Set lst = Me.ListBox7.Object
 
  With lst
    .ListStyle = fmListStyleOption
    .MultiSelect = fmMultiSelectMulti
    .ColumnCount = 2
    .ColumnWidths = "0;3"
    
    'Add first row to serve as column header
    Do While Not rs.EOF
      .AddItem rs!pk
      .List(i, 1) = rs!Fname  ' to add subsequent columns
      rs.MoveNext
      i = i + 1
    Loop
    
  End With
End Sub

the List property adds to another column.
I also could not get the column header to work in a multi column listbox

 

[CJ_London]

also could not get the column header to work in a multi column listbox

number of quetions
Is that the standard listbox?
can you use an alternative to the checkbox?
Can the checkbox be to the right?
Does this also work on the equivalent combo control?

 

[MajP]

Is that the standard listbox?

Depends what you mean. If you mean an Access lisbox then no. It is the standard if working in Excel or any other VBA product.

can you use an alternative to the checkbox?

You can use and option button if you set it to single select, but not in multiselect.

Can the checkbox be to the right?

Not as far as I can tell. I am pretty sure no.

Does this also work on the equivalent combo control?

Sort of. You can add a radio button. But neither that version or your version has any utility as far as I can tell. If you want a useable method see my multiselect combobox.

Solved - Unbound Combo box need multiple value

Hi, I have a searcher form. It is worked. But now like to put multiple value to search. CODE : Private Sub cmdFilter_Click() Dim strWhere As String Dim lngLen As Long If Not IsNull(Me.fltCompanyName) Then strWhere = strWhere & "([CompanyName] = """ &...

www.access-programmers.co.uk

 

[CJ_London]

Thanks

 

[CJ_London]

But neither that version or your version has any utility as far as I can tell

my combo version can show ticks or tickboxes - but not set up to be updateable. This example is a list of payees - the ticks indicate we have their bank details. Sorry for all the redactions - data confidentiality

 

[isladogs]

Very simple but effective. Thanks
Just to point out that the updateable listbox idea can also be applied to the first text only listbox without any changes.
No need to use a checkbox or similar to do this.

Private Sub StdListBox_AfterUpdate()

Const sqlStr = "UPDATE Table1 SET fSelected = Not [fSelected] WHERE PK="

     With StdListBox        
            CurrentDb.Execute sqlStr & .ItemData(.ListIndex - .ColumnHeads)
            .Requery
    End With

End Sub

 

[CJ_London]

Just to point out that the updateable listbox idea can also be applied to the first text only listbox

Yes I know, perhaps I should have phrased it better