How to add check boxes with vba

[44nato44]

Hi

Below I am populating a drop down with all files from a folder, I would like to change this to a list of check boxes.. so I can have users choose multiple values

Any body have an idea ?

Thank

Dim dir, folder, files
dir = "e:\WorkSpace\"
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(dir)
Set files = folder.files

' Print out my listbox

MyListBox.RowSourceType = "Value List"
For Each file In files
If Right(file, 4) = "xlsx" Then
MyListBox.AddItem (file.Name)
End If
Next

 

[PHV]

Why not using a MultiSelect ListBox ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[44nato44]

I find the check box's looks better

 

[dhookom]

If you don't want a list box, consider pushing the file names to a temporary table that contains a yes/no field. Users can then check the yes/no field to make their selections.

Duane
Hook'D on Access
MS Access MVP

 

[44nato44]

okay, but how would I write the check boxes to the form ?

 

[dhookom]

The same as you display any records in the form. If your main form isn't appropriate, use a continuous subform.

Duane
Hook'D on Access
MS Access MVP

 

[44nato44]

My thing is that I do not know how many check boxes I will get, it will be dertermine how many files are in the folder.

So if you know the vba code to add a check box on a specific place on a form, that would be highly appreciated.

Cheers

 

[MajP]

Dynamically creating controls in Access is usually frowned on. It can be done, but you can introduce a lot of issues. Why not use a listview that has checkboxes in it? It is a lot easier solution.

It is pretty easy to add controls to a form. So what part are you having trouble with? Where is your code so far?
1)Open the form in design view and hidden.
2)add control
3)set the controls properties
4)close and save the form
5)open the form
example

Public Sub addControl()
  Dim frm As String
  Dim chkbox As Access.CheckBox
  DoCmd.OpenForm "Orders", acDesign, , , , acHidden
  frm = "Orders"
  Set chkbox = Application.CreateControl(frm, acCheckBox, acDetail)
  With chkbox
    '.Left =
    '.Top =
    'other properties
  End With
  DoCmd.Close acForm, frm, acSaveYes
  DoCmd.OpenForm "Orders"
End Sub

I would not do this use a listview control or treeview control with built in checkboxes.

 

[dhookom]

I do not know how many check boxes I will get

That is the best justification I have heard for pushing the records to a table. You can then use a continuous subform with a vertical scroll bar. The table would be much easier to reference in queries and code. You could also use fields for file size and other attributes.

If you don't know how many check boxes, your form with created check boxes could grow unchecked.

Duane
Hook'D on Access
MS Access MVP