Excel VBA - Drop down box which lists all open excel workbooks

[largeselection]

Hello,

I'm blanking on the best way to do cleanly accomplish this. Basically I want a popup to ask the user which file to use. In the dropdown from the popup I would want it to list all open excel workbooks and then the rest of my code will establish that selected workbook as a name and then reference that for the rest of it.

Thanks for any help/insight/links you can share!

 

[Domenic]

Here's how it might be done using a UserForm...

1) Launch the Visual Basic Editor (Alt-F11).

2) Select 'Insert > Userform' from the menu.


3) Use the Control Toolbox to add the following four controls to the userform (first click on the desired control in the toolbox, such as the label control, and then click on the Userform to place it where desired)...

1. Label - This will contain text that asks the user to make a selection from the ComboBox.
2. ComboBox - This is where the user makes the selection.
3. CommandButton1 - This is where the user clicks after making the selection.
4. CommandButton2 - This is where the user clicks if he/she would like to cancel.

By the way, you can change the name that appears on CommandButton1 and CommandButton2 by individually clicking the button on the userform and changing the 'Caption' in the 'Properties Window'. So, for example, the 'Caption' for CommandButton1 could be changed to 'OK', and the 'Caption' for CommandButton2 could be changed to 'Cancel'.

4) Place the following code in the module for the userform (right-click the userform icon in the Project Explorer window or double-click on the userform itself)...

Option Explicit
 
Private Sub CommandButton1_Click()
    MyFile = Me.ComboBox1.Value
    Unload Me
End Sub
 
Private Sub CommandButton2_Click()
    Stopped = True
    Unload Me
End Sub
 
Private Sub UserForm_Initialize()
    Dim wkb As Workbook
    Me.Label1.Caption = "Please select one of the following files..."
    With Me.ComboBox1
        For Each wkb In Application.Workbooks
            .AddItem wkb.Name
        Next wkb
    End With
End Sub

5) Then, place this code in a regular module (Insert > Module)...

Option Explicit
 
Public MyFile As String
Public Stopped As Boolean
 
Sub test()
 
    Stopped = False
 
    UserForm1.Show
 
    If Stopped Then Exit Sub
 
    MsgBox MyFile
 
    'etc...
 
End Sub

6) Close the Visual Basic Editor (Alt+Q).

7) Run the macro named 'test' by bringing up the macro dialog box (Alt+F8), selecting 'test' and clicking on 'Run'.

 

[largeselection]

Thanks!

I was able to tailor this to do exactly what I need. Also thanks for such an in depth response!

I appreciate your help!

 

[largeselection]

How can I use this in a formula which I insert in VBA? So I have this formula which mostly works, but rather than putting in the value from the combobox1 value it leaves it blank.

"=INDEX('[" & ThisWorkbook.name & "]Part1!R24C24:R1001C24,MATCH(RC[-19],'[" & obk & "]Part1'!R24C1:R1001C1,FALSE),1)"

So instead of putting in the name of obk, it just leaves that part out.

So it will say Match (A19,Part1'!

#VALUE!

 

[Domenic]

Try...

UserForm1.obk.Value

Change the name of the userform, accordingly. Any reason why you have 'ThisWorkbook.Name' as the workbook reference for INDEX, and 'obk' as the workbook reference for MATCH?

 

[largeselection]

Holy geez. I guess at some point I muffed up the formula.

Thanks for pointing that out.

 

[furstukin]

I want to do something similar to the original poster but I am a bit of an newb at VBA. My goal is to get excel to ask the user which file they want to use then use that file to pull data from.

I have added all the code and it does pull up my userform and ask which file. then I added a little test and had it write the value I selected in the userform to a cell. All works fine. But when I try to get it to make that file the active workbook I get an error.

Option Explicit
 
Public Coastal1 As String
Public Stopped As Boolean
 
Sub test()
 
    Stopped = False
 
    UserForm1.Show
 
    If Stopped Then Exit Sub
 
    range("a1").Value = Coastal1
 
    Windows("Coastal1" & ".xlsm").Activate
 
 
End Sub

The error debug takes me to the Windows("Coastal1" & ".xlsm").Activate line which I was kind of making up since I am a newb at this. So how would I use the variable I created with the userform to activate that workbook?

Also if I wanted to have this repeat like 5 times, each time having the user select a different file will it keep updating my Coastal1 variable or do I need to have multiple variables?

Any help is appreciated thanks in advance.

 

[Domenic]

Try...

Windows(Coastal1 & ".xlsm").Activate

 

[konficus]

Thanks for the tip, i want to add this templete to my working code,if anyone can help it would be great;

My code;

Option Explicit
 
Private Sub CommandButton1_Click()
    MyFile = Me.ComboBox1.Value
    Unload Me
End Sub
 
Private Sub CommandButton2_Click()
    Stopped = True
    Unload Me
End Sub
 
Private Sub UserForm_Initialize()
    Dim wkb As Workbook
    Me.Label1.Caption = "Aşağıdaki dosyalardan birini seçiniz..."
    With Me.ComboBox1
        For Each wkb In Application.Workbooks
            .AddItem wkb.Name
        Next wkb
    End With
End Sub

And alter selection of which excel file will continue to process i would like to choose it and next code is;

Option Explicit
 
Public MyFile As String
Public Stopped As Boolean
Sub test()
Stopped = False
 
    DosyaAdi.Show
 
    If Stopped Then Exit Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim GOA As Workbook
Set GOA = MyFile <= HERE I WANT TO CALL WHICH FILE I WANT TO PROCESS, BELOW GOA DEFINED AS WORKBOOK AND I WANT TO CHOOSE WORKBOOK FROM DOPDOWN MENU.
Sayfasayisi = GOA.Sheets.Count
p = 1
    Application.SheetsInNewWorkbook = Sayfasayisi
    Workbooks.Add
    For i = 1 To Sayfasayisi
    If i > Sayfasayisi Then GoTo devam
    Sheets(i).Select
    Sheets(i).Name = GOA.Sheets(p).Name
    GOA.Sheets(p).Columns(8).Copy
    Sheets(i).Cells(1, 8).PasteSpecial
    If i = 1 Then
    GOA.Sheets(1).Range("$A$1:$e$7000").AutoFilter Field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
    GOA.Sheets(1).Cells(1, 1).Parent.AutoFilter.Range.Copy
    Sheets(i).Cells(1, 1).PasteSpecial