Dynamic Form CheckBoxes Controlls

[itipu]

Hi All,

Would appreciate some tips or help.. Basically what my code in the sample does it on click of the button it asks user to open a folder, gets names of all excel files in the folder, dynamically creates a form and adds checkboxes with file names to that folder.

Now I add checkboxes as follows:

[VBA]For lngFileIndex = lngLwrBnd_c To fs.FoundFiles.Count
Set wbcrnt = Excel.Workbooks.Open(fs.FoundFiles(lngFileIndex), False, _
False, Password:="foo")
Dim mycheckbox As Object

Set mycheckbox = frmSelect.Designer.Controls.Add("Forms.CheckBox.1")
With mycheckbox
.Caption = wbcrnt.Name
.Left = 10
.Top = 10 + (30 * i)
.Height = 20
.Width = 300
End With
wbcrnt.Close

i = i + 1
Next[/VBA]

So that creates as many checkboxes as there are files in the folder.

Ideally I want to than find out which are checked and their names, so that I can manipulate selected files afterwards.. So far I tried something like that but nothing seems to work:

[VBA]
Dim w As Integer

For w = 1 To i
With frmSelect.CodeModule
x = .CountOfLines
.InsertLines x + 1, "Public Sub ChBx()"
.InsertLines x + 2, " If frmSelect.controls("checkbox" & w).Value = True Then"
.InsertLines x + 3, " msgbox 7"
.InsertLines x + 4, " end if"


End With

MsgBox "checked"
End If
Next w[/VBA]

Any suggestion would be much appreciated..

The other small thing is, at the moment to get file names I use the following:

[VBA]Set wbcrnt = Excel.Workbooks.Open(fs.FoundFiles(lngFileIndex), False, _
False, Password:="foo")[/VBA]

and

[VBA]With mycheckbox
.Caption = wbcrnt.Name[/VBA]

However that also opend xls files in the folder, I was not able to figure out how to do the same and NOT open files but also NOT get the full path in the name, eg - "fs.FoundFiles"

Thanks a lot

mike

[JimmyTheHand]

Mike,

I think you are on the wrong track. Let anyone who knows better prove me wrong, but the controls you are creating on a form run-time will not trigger event handler routines also created at run-time. It's something about how the compiler works. You need to end code execution first, before those newly added event handlers are taken into account.

I recommend a different approach.

• Create the form at design time.
• Add a listbox, still at design time. Set the properties this way:
  • MultiSelect = fmMultiSelectMulti
  • ListStyle = fmListStyleOption
• Add the list of files to the listbox at run time.

This way you get essentially the same picture in the end as you would with run time created checkboxes.

Selecting a folder is, IMO, best done this way:

[vba]Function SelectedFolder()
SelectedFolder = False 'Returns False value when user clicks 'Cancel' button
On Error Resume Next
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
SelectedFolder = .SelectedItems(1)
End With
End Function
[/vba]
Iterating through the files and adding them to the list:
[vba]Sub AddFilesToList()
Dim Fldr, FN As String
Fldr = SelectedFolder
If Fldr = False Then Exit Sub
FN = Dir(Fldr & "\" & "*.xls", vbNormal)
While Not FN = ""
UserForm1.ListBox1.AddItem FN
FN = Dir()
Wend
End Sub[/vba] Use Listbox.Selected(i) property for determining if the "checkbox" was checked.

HTH

Jimmy

[itipu]

Jimmy,

Thanks, I was thinking I was going to far, I see what you mean with ListBoxes, however I tried your SelectFolder and seems like I am missing some declarations/references or I am not clear in general how it works...

If I just use your function, absolutely nothing happens, if I debug line by line it just goes through them without anything appearing on the screen.. so not sure whats missing!

Thx a lot

Mike

[JimmyTheHand]

I suppose there is an error, but you don't get it because of the error handler (On Error Resume Next)
Try disabling the error handler like this:

[vba]Function SelectedFolder()
SelectedFolder = False 'Returns False value when user clicks 'Cancel' button
'On Error Resume Next
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
SelectedFolder = .SelectedItems(1)
End With
End Function [/vba]
As for the cause, I'm not sure, but it might be version problem. The code should work with Excel 2003, but does definitely not work with Excel 2000. I don't know about 2002.

But you have your own method for selecting folders, so if mine doesn't work, stay with yours.

Jimmy