excel - Recall User Selections Within Multiple Modules -

excel - Recall User Selections Within Multiple Modules -

my current code uses workbook_open event run userform asks user wants do, if select "a" populates userform listbox displays open workbooks.name(s). using listbox_dblclick event, want user able double-click name of workbook wish create active run tasks involve copying info 1 sheet (a workbook exported temporary folder semi-impossible naming system) workbook know name , location of. cannot seem listbox value recalled after double-click event. pointers?

code within userform2:

option explicit public vval string private sub listbox1_dblclick(byval cancel msforms.returnboolean) vval = me.listbox1.value phone call automateme unload me end sub private sub userform_initialize() dim wkb workbook me.listbox1 each wkb in application.workbooks .additem wkb.name next wkb end end sub

the sub double-click event calls (the 1 having problems with) is:

sub automateme() 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 end sub

thank help!

firstly, should using public variable vval assigned in dblclick event. public variables persist, userform values don't.

next, have tell vba value workbook, workbooks(vval)

lastly, within of with should performing actions on with object.

so if mydata worksheet in vval workbook:

with workbooks(vval) .worksheets("mydata").range("d2:d103").select end

note period prefixes worksheets

excel vba excel-vba listboxitems automated-deployment