[VBA] Custom two-option user Popup

sbawnh

Board Regular
Joined
Feb 25, 2019
Messages
50
Office Version
  1. 365
Platform
  1. Windows
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>
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.

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
 
Upvote 0
Ah I see that you are being helped already

deleted by Yongle
 
Last edited:
Upvote 0
DanteAmor, Thank you for your assistance. I'd prefer to use two buttons instead.

If this was possible, it would be ideal
Code:
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.
Code:
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:
[CODE]
Sub SelectButton_Click()
    Call Module1.SelectFiles(MyfilesForm)
End Sub

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

'rest of code
[/CODE]
 
Last edited:
Upvote 0
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.
 
Upvote 0
Check if this helps you:

Code:
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
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top