Recall User Selections Within Multiple Modules

Question

My current code uses the Workbook_Open event to run a userform that asks what the user wants to do, if they select "A" it populates a userform with a listbox that displays all open workbooks.name(s). Using the ListBox_DblClick event, I want the user to be able to double-click the name of the workbook that they wish to make active to the run some tasks that involve copying information from one sheet (a workbook that is exported to a temporary folder with an semi-impossible naming system) into a workbook that I know the name and location of. I cannot seem to get the listbox value to be recalled after the double-click event. Any pointers?

Code within Userform2:

Option Explicit
Public Vval As String
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Vval = Me.ListBox1.Value
Call AUTOMATEME
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim wkb As Workbook
With Me.ListBox1
    For Each wkb In Application.Workbooks
        .AddItem wkb.Name
    Next wkb
End With
End Sub

The sub that the double-click event calls (the one that I am having problems with) is:

Sub AUTOMATEME()

With Listbox1.Value
Worksheets("MYDATA").Range("D2:D103").Select
Selection.Copy
Workbooks("ALL_Data.xlsm").Worksheets("FORMULAS").Select
Range("G2").Select
ActiveSheet.Paste
Sheets("FORMULAS").Select
ListBox1.Value.Select
Range("E2:E103").Select
Selection.Copy
Workbooks("ALL_Data.xlsm").Worksheets("FORMULAS").Select
Range("G2").Select
Range("E2").Select
ActiveSheet.Paste
End With
End Sub

Thank you for any help!

score 1 · Accepted Answer · answered Apr 08 '15 at 18:33

1

Firstly, you should be using the public variable vVal that you assigned in the DblClick event. Public variables persist, userform values don't.

Next, you have to tell VBA that the value is a workbook, Workbooks(vVal)

Lastly, inside of a With you should only be performing actions on the With object.

So if MYDATA is a worksheet in the vVal workbook:

With Workbooks(vVal)
   .Worksheets("MYDATA").Range("D2:D103").Select
End With

Note the period that prefixes Worksheets

answered Apr 08 '15 at 18:33

Emily Frouin

36
4

Thank you for your suggestion! I have implemented this change and am getting a Compile Error: Method or data member not found that refers to the: "With Workbooks(Vval)" line... I have stored the public variable within the beginning of the Module. – user3794203 Apr 08 '15 at 21:03
When someone selects a value in Listbox1, what does the value look like in Vval? – shagans Apr 09 '15 at 21:09
@shagans - Do you mean in the Immediate window? – user3794203 Apr 10 '15 at 15:38
I mean during run-time debug. I ask because it's possible what vVal is storing is not a valid format for Workbooks(vVal) so knowing what the string is that you're passing in will help suss out potential issues. – shagans Apr 10 '15 at 18:59

Recall User Selections Within Multiple Modules

1 Answers1