[VBA] Custom two-option user Popup

sbawnh

New Member
Joined
Feb 25, 2019
Messages
31
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,945
Office Version
2007
Platform
Windows
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,296
Ah I see that you are being helped already

deleted by Yongle
 
Last edited:

sbawnh

New Member
Joined
Feb 25, 2019
Messages
31
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,945
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,945
Office Version
2007
Platform
Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,945
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,078,491
Messages
5,340,683
Members
399,389
Latest member
JayNExcel

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top