[VBA] Custom two-option user Popup

[sbawnh]

Hi all,

Hope you're having a good day.

I want to create a popup for a user to choose between three options

Select a file, Select a folder, Cancel

I made a userform to handle this.

If they choose to select the first option, I want to use Application.FileDialog(msoFileDialogFilePicker)
to load 1 - X file names into an array, then continue with each file from the array

If they choose the second option, I want to use Application.FileDialog(msoFileDialogFolderPicker)
to load the folder, then scan the folder etc etc.

I thought to create a userform with two buttons, each Goto SelectFile or Goto ChooseFolder, but in my searches people said its better to direct the button to a separate sub, than to the middle of a sub (via goto)

So I put my msoFileDialogs in different subs, but how can I move the array back to the main sub?

Thank you!!

Edit: The second sub works to load the array into the main, but I just need help with connecting this all with the userform

Private Sub SelectButton_Click()


Call SelectFiles(MyFiles)


Me.Hide


End Sub

It doesn't know what Myfiles is
So if I add Dim MyFiles() as String
it will work, but when I return back to the main sub, i'll get "expression not defined in context" <expression not="" defined="" in="" context=""> in the Watches</expression>

 

[DanteAmor]

You can create a combobox with 2 options, if the user chooses the first one, he can select a file, if selecting the second one he will select a folder.
Everything would be in one code.

Private Sub ComboBox1_Change()
  Dim sItem As Variant, files As New Collection, wfiles As Variant, i As Long
  If ComboBox1.ListIndex = -1 Or ComboBox1.Value = "" Then Exit Sub
  If ComboBox1.ListIndex = 0 Then
[COLOR=#0000ff]    With Application.FileDialog(msoFileDialogFilePicker)[/COLOR]
        .Title = "Select Files"
        .Filters.Add "Excel Files", "*.xls*"
        .AllowMultiSelect = True
        .InitialFileName = "C:\trabajo\books\"
        If .Show Then
          For Each sItem In .SelectedItems()
            files.Add sItem
          Next
        End If
    End With
  ElseIf ComboBox1.ListIndex = 1 Then
[COLOR=#0000ff]    With Application.FileDialog(msoFileDialogFolderPicker)[/COLOR]
      .Title = "Select Folder"
      .AllowMultiSelect = False
      .InitialFileName = "C:\trabajo\books\"
      If .Show Then
        wfiles = Dir(.SelectedItems(1) & "\*.xls*")
        Do While wfiles <> ""
          files.Add .SelectedItems(1) & "\" & wfiles
          wfiles = Dir()
        Loop
      End If
    End With
  End If
  For i = 1 To files.Count
[COLOR=#008000]    '
    'Here your code to perform the activities with each file in the array.
    '[/COLOR]
    Workbooks.Open files.Item(i)
  Next
End Sub


Private Sub UserForm_Activate()
  ComboBox1.AddItem "Select a file"
  ComboBox1.AddItem "Select a folder"
End Sub

 

[Yongle]

Ah I see that you are being helped already

deleted by Yongle

 

[sbawnh]

DanteAmor, Thank you for your assistance. I'd prefer to use two buttons instead.

If this was possible, it would be ideal

Sub SelectButton_Click()
Me.Hide
Goto MainSubToSelectFiles
End Sub

Let me maybe reword my question now that i've figured a couple things out
This works perfectly, it loads my files into an array for processing.

Sub Main()
    'Above code
    Call Module1.SelectFiles(MyfilesForm)

    MyFiles() = MyfilesForm()
    'Rest of code
End Sub

Is it possible to put "Call Module1.SelectFiles(MyfilesForm)" into the CommandButton?
as

[CODE]
Sub SelectButton_Click()
    Call Module1.SelectFiles(MyfilesForm)
End Sub

Sub main()
'Above Code
Userform.Show
Myfiles() = MyfilesForm

'rest of code

[/CODE]

 

[DanteAmor]

I don't understand where you are going to put the 2 buttons, on the sheet or inside the userform?
If you are going to open a userform why do you want a module?
You can do everything within the userform.

 

[DanteAmor]

Check if this helps you:

Private Sub [COLOR=#0000ff]CommandButton1_Click[/COLOR]()
  Dim sItem As Variant, files As New Collection, wfiles As Variant, i As Long
  With Application.FileDialog(msoFileDialogFilePicker)
      .Title = "Select Files"
      .Filters.Add "Excel Files", "*.xls*"
      .AllowMultiSelect = True
      .InitialFileName = "C:\trabajo\books\"
      If .Show Then
        For Each sItem In .SelectedItems()
          files.Add sItem
        Next
      End If
  End With
  If files.Count > 0 Then
[COLOR=#ff0000]    Call process(files)[/COLOR]
  End If
End Sub


Private Sub [COLOR=#0000ff]CommandButton2_Click[/COLOR]()
  Dim sItem As Variant, files As New Collection, wfiles As Variant, i As Long
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    .AllowMultiSelect = False
    .InitialFileName = "C:\trabajo\books\"
    If .Show Then
      wfiles = Dir(.SelectedItems(1) & "\*.xls*")
      Do While wfiles <> ""
        files.Add .SelectedItems(1) & "\" & wfiles
        wfiles = Dir()
      Loop
    End If
  End With
  If files.Count > 0 Then
[COLOR=#ff0000]    Call process(files)[/COLOR]
  End If
End Sub


[COLOR=#ff0000]Sub process(files)[/COLOR]
  Dim i As Long
  For i = 1 To files.Count
[COLOR=#008000]    '[/COLOR]
[COLOR=#008000]    'Here your code to perform the activities with each file in the array.[/COLOR]
[COLOR=#008000]    '[/COLOR]
    Workbooks.Open files.Item(i)
  Next
End Sub

 

[sbawnh]

Thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.