Solved: looping through check boxes on a form

[philfer]

I am using an Excel workbook to import data from other Excel downloads and then perform a variety of actions on it.

I have a hidden sheet with the paths of the downloads and an Access DB which I also use.

I want to use a form which the user can use to change the paths to the downloads and/or DB. My questions are :-

1) How can I get it so that when the form loads it fills labels on the form with the name of the download and, separately, the path. With check boxes next to each

2) How can I program a button that selects all the check boxes i.e. puts ticks in each one

3) How can I run a loop that says for each check box the user has ticked, run a loop that many times, which opens the FileDialog box, and puts the path into the hidden sheet (calls "Paths") so that when they import the stuff the next time the FileDialog used for the import knows the default path for each separate download

Thanks

[BenD]

2) How can I program a button that selects all the check boxes i.e. puts ticks in each one

Item 1 & 3 require quite a lot of coding!

For 2 you could use something like the following:

[vba]
Private Sub cbSelectAllProds_Change()

Dim ctlControl As MSForms.Control

If mbEnableEvents = True Then
For Each ctlControl In mpMultiPage.Pages(0).Controls
If TypeName(ctlControl) = "CheckBox" Then
If ctlControl.Name = "cbSelectAllProds" Then
ctlControl.Enabled = False
Else
ctlControl.Value = True
End If
End If
Next ctlControl
End If

Set ctlControl = Nothing
End Sub[/vba]

In this case is 'cbSelectAllProds' a MSForms.CheckBox instead of a button, and the routine loops through checkboxes on a page of a MultiPage control. You could use:
[vba]For Each ctlControl In Controls[/vba]
instead to loop through all checkboxes on the form.

[philfer]

would i need to reference the UserForm that the controls are on i.e.

For Each ctlControl In UserForm.Controls

??

[Bob Phillips]

[vba]

'1)

With Me

.lblDownload.Caption = Worksheets("Hidden").Range("A1").Value
.lblAccessDB.Caption = Worksheets("Hidden").Range("A1").Value
end With
[/vba]

[vba]

'3)
Dim ctl As MSForms.Control


With Me

For Each ctl In .Controls

If Typname(ctl) = "CheckBox" Then

If ctl.Value Then

mppath = Application.FileDialog(msoFileDialogFolderPicker)
Worksheets("Hidden").Range("A1").End(xlDown).Value = mppath
End If
End If
Next ctl
End With
[/vba]

[rconverse]

I am using an Excel workbook to import data from other Excel downloads and then perform a variety of actions on it.

I have a hidden sheet with the paths of the downloads and an Access DB which I also use.

I want to use a form which the user can use to change the paths to the downloads and/or DB. My questions are :-

1) How can I get it so that when the form loads it fills labels on the form with the name of the download and, separately, the path. With check boxes next to each

2) How can I program a button that selects all the check boxes i.e. puts ticks in each one

3) How can I run a loop that says for each check box the user has ticked, run a loop that many times, which opens the FileDialog box, and puts the path into the hidden sheet (calls "Paths") so that when they import the stuff the next time the FileDialog used for the import knows the default path for each separate download

Thanks

I would suggest creating a table in Access with filepaths in it and on the open event of a form fill in the labels or you can have the labels filled based on the results of a combo box, listbox, etc.

For example, in a given form, I may have 5 reports. From a combobox, I can pick a report and based on that, the correct filepath appears. If that is not the correct filepath (for whatever reason) the user can enter their own. If that is the case, you could then let the user choose whether or not they want the default path updated for the next run. It is a lot easier to do this all in Access then trying to go back and forth from Excel to Access for filepaths.

Thanks,
Roger

[rory]

Why not use a listbox? You can set its list style to show options and it doesn't matter how many file paths you have to deal with - you just load them all into the one control. You can make it multiselect easily too.