dimanche 12 avril 2020

VBA loop through checkboxes and open a file starting with Checkbox value

I need too loop through checkboxes and open file (on a given directory) starting with the name of checkbox. (ex. Checkbox value = A, then open file A_xxx.xlsx)

The directory is also not static, some folders are selected by combobox. it is left to open file(s) if one or more checkboxes are selected. I tried to write an example for checkbox2 and its not working.

 Private Sub CommandButton1_Click()
    Dim sFolder As String
    Dim initial As String
    Dim initial_path As String
    Dim name As String
    Dim i As Control

' check for completion:


 If Me.ComboBox1.Value = "" Then MsgBox "Please complete all inputs": Exit Sub
    If Me.ComboBox3.Value = "" Then MsgBox "Please complete all inputs": Exit Sub
    'If initial = "" Then MsgBox "Please complete all inputs": Exit Sub

' now check initial path exists:
initial_path = "C:\Users\ra00036337\Desktop\My files" & "\" & Me.ComboBox1.Value & "\" & Me.ComboBox3.Value & "\"
If Dir(initial_path, vbDirectory) = "" Then MsgBox "Directory not found": Exit Sub
name = "Raw"
name = Dir(initial_path & name & "*", vbDirectory)
If Len(name) Then

For Each i In Me.Frame2.Controls
    Debug.Print TypeName(i)
    If TypeName(i) = "Checkbox" Then
     If Me.CheckBox2.Value = True Then
      Workbooks.Open Filename:=(initial_path & name & "*" & "A*" & "*.xlsx")
 End If
 End If
 Next i

End If
End Sub



Aucun commentaire:

Enregistrer un commentaire