Excel VBA - List Box that lists asks you to select which workbook to activate.

[furstukin]

Ok so I have found a very useful bit of code from another post on here

http://www.mrexcel.com/forum/showthread.php?p=2693297#post2693297

and I am trying to modify it to work for my purposes. The code in question creates a user form that has a combo box that will list all open workbooks and ask you to select one. Now I have modified the code a bit to change the variable name and to make it a list box rather than a combo box. And now my code looks like this.

In the User Form Module I have this

Option Explicit
 
Private Sub CommandButton1_Click()
    Coastal1 = Me.ListBox1.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.ListBox1
        For Each wkb In Application.Workbooks
            .AddItem wkb.Name
        Next wkb
    End With
End Sub

In the actual Module for the macro I am making/testing I have this:

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

Now what I want to happen is have it ask me which workbook I want to use and then what ever workbook I choose I want that to be the active workbook so I can then start selecting ranges and have the macro pull data from the selected workbook and put it on the one the macro was run in.

My issue is every month we get files with new names so I am trying to avoid having to change the hard coded names for the workbooks each month.

Again any help is appreciated. Thanks.

 

[ColinKJ]

Hi furstukin,

You don't need a Userform to do that.

Are you able to post an example of one of the data WB's, and an example of how you want the data displayed on your MAster WB.

 

[furstukin]

Hi furstukin,

You don't need a Userform to do that.

Are you able to post an example of one of the data WB's, and an example of how you want the data displayed on your MAster WB.

Well it turns out my issue was the quotation marks around the variable name.

And I am not sure I can post any of the data from my WB's. It is all sensitive material. But basically I have a macro sheet that pulls data from 8 different spreadsheets. Right now the macro is hardcoded with filenames for each workbook name, but those names change so I have to keep changing my macro code. All I want to do is change it from a hard coded file name and make the macro ask the user which open file they want to use. That way I can tell the user have all files opens and the macro will ask which file you want to use. So then all we have to do is select the files in order and it will run the macro extracting data as normal.

I am not sure how I can accomplish this with out a user form, but if you can give me an example I would appreciate it. Thanks.

 

[ColinKJ]

Hi furstukin,

I've put together an example WB called Copy To Master which you can download from:

http://www.box.net/shared/e8tl5yc36b

With the above WB open, open an number of other WBs.

Select the Copy To Master and press Ctrl+q, a List Box will be displayed with a list of all open WB (except Copy To Master ).

If you look at the Listbox1 DoubleClick code, this is where you would call your Code.