vba - How can I utilize a specific variable based on a conditional statement? -

i have macro displays userform create reconciliation report depending on report user decides want generated. (i.e. if user selects reconciliation credit cards, credit card report generated. if select checks & credit cards, report created both credit cards , checks -- in same master report summary)

once checkboxes either of reports selected in initial form (or deselected) boolean variable account toggled. once user proceeds selected reports, report generated each selection. (the first sheet summary, each consecutive sheet contains source files , balancing calculations each report -- capped summary sheet)

refer first image.

..

after report completed, if there differences found between 2 accounts being reconciled (bank statement vs. our accounting records), userform displayed showing differences, presented in 2 separate listboxes; 1 listbox contains values our records, other listbox contains values bank statement.

refer second & third image.

..

the post-report userform has 2 buttons each listbox; 1 button allows user remove selected [listbox] item list if balances out item/multiple items in other listbox. if not match, have reflect in our records, , second button allows them reflect having done in daily reconciliation report.

for macro, have created variables formatted "bwvariable", "chvariable", "echvariable" or "ccvariable" each possible report affect report's items. through testing, however, have used bwvariables easiest test with.

now it's finished, want outcome of button_click() event use bwvariables if bwreport true, , if ccreport true, want use ccvariables. there way use specific variables depending on output of conditional statement?

..

here's code affected:

private sub removedfromgpbutton_click()     dim bwitem double      litem = bwlistbox.listcount - 1 0 step -1         if bwlistbox.selected(litem)             bwitem = bwlistbox.list(litem, 0)             bwlistbox.removeitem litem             if bwaddedgpsum2 nothing                 set bwaddedgpsum2 = range(bwgpsum.offset(1, -3), bwgpsum.offset(1, 1))                 bwaddedgpsum2.insert shift:=xldown                 set bwaddedgpsum2 = range(bwgpsum.offset(1, -3), bwgpsum.offset(1, 1))                 bwaddedgpsum2.interior.colorindex = 0                 bwaddedgpsum2.insert shift:=xldown                 set bwaddedgpsum2 = range(bwgpsum.offset(1, -3), bwgpsum.offset(1, 1))                 bwaddedgpsum2.interior.colorindex = 0                 set bwaddedgptitle2 = range(bwgpsum.offset(1, -2), bwgpsum.offset(1, -1))                 bwaddedgptitle2                     .mergecells = true                     .horizontalalignment = xlright                     .verticalalignment = xlcenter                 end                     bwaddedgptitle2.value = "added deposit:"                 set bwaddedgpsum2 = bwgpsum.offset(1, 0)                     if bwaddedgpsum nothing                         bwaddedgpsum2.borders(xledgebottom).linestyle = xlcontinuous                     end if                 if bwgpsum.offset(-1, 0).text = ""                     set bwgpsum = bwvariance.offset(-2, 0)                         range(bwgpsum.offset(0, -1), bwgpsum.offset(0, -2)).mergecells = true                         bwgpsum.offset(0, -1).horizontalalignment = xlright                         bwgpsum.offset(0, -2).value = "total:"                         bwgpsum.interior.colorindex = 6                 end if             end if             bwaddedgpsum2.value = format((bwaddedgpsum2.value + bwitem), "$#,##0.00")             bwgpsum.value = format(application.worksheetfunction.sum(bwgpsum.offset(-1, 0).end(xlup), _                                    bwgpsum.offset(-1, 0)), "$#,##0.00")             call determinevariance             if me.bwlistbox.multiselect = fmmultiselectsingle                 exit             end if          end if     next end sub 

..

first image:

..

second image:

..

third image:

there's no way query/refer variable name part in vba

so i'd suggest to:

• use user defined type (say call "report") fields names matches variables names constant part (listbox, item, addedgpsum, addedgpsum2,...)

• declare public variable (say calle rep) of type "report" in of modules

  it'll used/shared between userforms

• declare userform scoped named variables (bw, ch, ech, cc) of type "report" in reportgenerator userform

this way removedfromgpbutton_click sub code have minor modifications:

• wrap inside with rep , end with block

• replace bw .

in bit more detail:

---

main sub

here follows main sub essential excerpt:

option explicit  type report '<-- define user defined type 'report'     listbox msforms.listbox     item string     addedgpsum range     addedgpsum2 range     addedgptitle2 range     gpsum range     variance range     '     ' , whatever else may need     ' end type  public rep report '<--| declare public variable of type 'report'   sub main() 'this sub exploit 'reportgenerator' userform      '     ' code preceeding 'reportgenerator' userform exploitation     '      reportgenerator '<--| load 'reportgenerator' userform...         ' code initialize/set userform controls (you may want take 'userform_initialize' sub)          .show '<--| ... , show          '         ' possible code exploit 'reportgenerator' userform outcome         '     end      '     ' code after 'reportgenerator' userform exploitation     '   end sub 

---

reportgenerator userform

here follows reportgenerator userform code essential excerpt:

option explicit  dim bw report, ch report, ech report, cc report '<--| declare userform scoped variables. they'll available throughout whole 'reportgenerator' userform subs , functions   private sub okbutton_click() '<--| change "okbutton" actual 'reportgenerator' userform button name "ok" caption      me '<--| refer 'reportgenerator' userform         select case true '<--| check first checked checkbox...             case .checkbox1                 rep = bw '<--|... , assign proper variable of type "report" public variable rep of type "report"             case .checkbox2                 rep = ch '<--|... , assign proper variable of type "report" public variable rep of type "report"             case .checkbox3                 rep = ech '<--|... , assign proper variable of type "report" public variable rep of type "report"             case .checkbox4                 rep = cc '<--|... , assign proper variable of type "report" public variable rep of type "report"         end select         .hide '<--| hide 'reportgenerator' userform before showing "unmatched_summary" 1          unmatched_summary.show ' <--| load , show "unmatched_summary" userform         unload unmatched_summary  ' <--| unload "unmatched_summary" userform          .show ' <--| show 'reportgenerator' userform again     end   end sub   private sub userform_initialize() '<--| runs @ userform loading time     me '<--| refer 'reportgenerator' userform         set bw.listbox = .listbox1 '<--| set bw listbox appropriate variable of type "report"         set ch.listbox = .listbox2 '<--| set ch listbox appropriate variable of type "report"         set ech.listbox = .listbox3 '<--| set ech listbox appropriate variable of type "report"         set cc.listbox = .listbox4 '<--| set cc listbox appropriate variable of type "report"     end end sub 

---

unmatched summary userform

here follows unmatchedsummary userform code essential excerpt

option explicit  private sub removedfromgpbutton_click()     dim litem long      rep '<--| refer public variable of tipe "report" has been previuosly set proper 1 in 'reportgenerator' before 'unmatched_summary.show' statement         litem = .listbox.listcount - 1 0 step -1             if .listbox.selected(litem)                 .item = .listbox.list(litem, 0)                 .listbox.removeitem litem                 if .addedgpsum2 nothing                     set .addedgpsum2 = range(.gpsum.offset(1, -3), .gpsum.offset(1, 1))                     .addedgpsum2.insert shift:=xldown                     set .addedgpsum2 = range(.gpsum.offset(1, -3), .gpsum.offset(1, 1))                     .addedgpsum2.interior.colorindex = 0                     .addedgpsum2.insert shift:=xldown                     set .addedgpsum2 = range(.gpsum.offset(1, -3), .gpsum.offset(1, 1))                     .addedgpsum2.interior.colorindex = 0                     set .addedgptitle2 = range(.gpsum.offset(1, -2), .gpsum.offset(1, -1))                     .addedgptitle2                         .mergecells = true                         .horizontalalignment = xlright                         .verticalalignment = xlcenter                     end                         .addedgptitle2.value = "added deposit:"                     set .addedgpsum2 = .gpsum.offset(1, 0)                         if .addedgpsum nothing                             .addedgpsum2.borders(xledgebottom).linestyle = xlcontinuous                         end if                     if .gpsum.offset(-1, 0).text = ""                         set .gpsum = .variance.offset(-2, 0)                             range(.gpsum.offset(0, -1), .gpsum.offset(0, -2)).mergecells = true                             .gpsum.offset(0, -1).horizontalalignment = xlright                             .gpsum.offset(0, -2).value = "total:"                             .gpsum.interior.colorindex = 6                     end if                 end if                 .addedgpsum2.value = format((.addedgpsum2.value + .item), "$#,##0.00")                 .gpsum.value = format(application.worksheetfunction.sum(.gpsum.offset(-1, 0).end(xlup), _                                        .gpsum.offset(-1, 0)), "$#,##0.00")     '            call determinevariance                 if .listbox.multiselect = fmmultiselectsingle exit              end if         next litem     end  end sub 

and, can see, took little modifications compared original removedfromgpbutton_click() sub

edit check checkboxes

to check , act checkboxes here follows how changes relevant excerpt of reportgenerator userform code:

with me '<--| refer 'reportgenerator' userform     .hide '<--| hide 'reportgenerator' userform before showing "unmatched_summary" 1     if .checkbox1         rep = bw '<--|... , assign proper variable of type "report" public variable rep of type "report"         unmatched_summary.show ' <--| load , show "unmatched_summary" userform     end if     if .checkbox2         rep = ch '<--|... , assign proper variable of type "report" public variable rep of type "report"         unmatched_summary.show ' <--| load , show "unmatched_summary" userform     end if     if .checkbox3         rep = ech '<--|... , assign proper variable of type "report" public variable rep of type "report"         unmatched_summary.show ' <--| load , show "unmatched_summary" userform     end if     if .checkbox4         rep = cc '<--|... , assign proper variable of type "report" public variable rep of type "report"         unmatched_summary.show ' <--| load , show "unmatched_summary" userform     end select      unload unmatched_summary  ' <--| unload "unmatched_summary" userform      .show ' <--| show 'reportgenerator' userform again end