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!
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