Excel Macro Process Bar -
i have consolidation summary have struggled through , have made work (though takes time) , wanted add in progress bar users know excel working. finding difficult 1.) add in needed vba bar run correctly , 2.) not able figure out how keep form window open while background macro running see progression.
a little background: summary sheet updated matching headers 50+ worksheets same workbook. there around 700k lines being consolidated (i assumed that's why takes long) changed .copy transferring data.
my rows both text , numbers , having hardest time figuring out how make progress bar work. examples see reference numbers (ie. 1 - 1000).
i have attempted both form progress bar (framing & labels) , status bar. take either if can work!!
here current(non-working) code. without progress bar code, macro runs fine.
i including associated models/forms flow not correct either.
this button command excel sheet:
sub openform() main.show end sub this brings "main" form 2 buttons "run consolidation" & "cancel"
this next bit of code think chain broke. code move consolidation macro when selected form disappears.
private sub commandbutton2_click() 'cancel button unload me end sheet2.activate end sub private sub consolidation_click() 'run consolidation button consolidate end sub private sub progressbar_mousedown(byval button integer, byval shift integer, byval x stdole.ole_xpos_pixels, byval y stdole.ole_ypos_pixels) end sub i not sure how private sub progressbar tied in here. introductory levels of vba show.
option compare text option explicit sub consolidate() dim demanddata worksheet dim destsh worksheet dim arow range dim acol range dim myincol range dim myoutcol range dim cell range dim iloop long, jloop long dim nextrow long dim long, rowmaximum long, colmaximum long dim r long, c integer dim percent single dim wordcount long application.screenupdating = false progressbar2.min = 0 progressbar2.max = 1 progressbar2.visible = true = 0 rowmaximum = 10 colmaximum = 26 r = 1 rowmaximum c = 1 colmaximum cells(r, c).select next c percent = / (rowmaximum * colmaximum) pregressbar2.value = percent next r jloop = 2 ' loop through worksheets each demanddata in activeworkbook.worksheets ' pick worksheets of interest if lcase(left(demanddata.name, 6)) = "demand" progressbar2.max = counta(sheet3!range1) + counta(sheet4!range1) ' find columns of interest in worksheet each acol in demanddata.usedrange.columns set myoutcol = nothing if acol.cells(1, 1).value = "region name" set myoutcol = sheets("consolidation").range("b:b") if acol.cells(1, 1).value = "location_code" set myoutcol = sheets("consolidation").range("c:c") if acol.cells(1, 1).value = "location_name" set myoutcol = sheets("consolidation").range("d:d") if acol.cells(1, 1).value = "dealer_code" set myoutcol = sheets("consolidation").range("e:e") if acol.cells(1, 1).value = "order_type_code" set myoutcol = sheets("consolidation").range("f:f") if acol.cells(1, 1).value = "pick_up" set myoutcol = sheets("consolidation").range("g:g") if acol.cells(1, 1).value = "create_date" set myoutcol = sheets("consolidation").range("h:h") if acol.cells(1, 1).value = "invoice_date" set myoutcol = sheets("consolidation").range("i:i") if acol.cells(1, 1).value = "item_number" set myoutcol = sheets("consolidation").range("j:j") if acol.cells(1, 1).value = "long_part" set myoutcol = sheets("consolidation").range("k:k") if acol.cells(1, 1).value = "part_description" set myoutcol = sheets("consolidation").range("l:l") if acol.cells(1, 1).value = "item_product_code" set myoutcol = sheets("consolidation").range("m:m") if acol.cells(1, 1).value = "inv_acct_type" set myoutcol = sheets("consolidation").range("n:n") if acol.cells(1, 1).value = "serial_number" set myoutcol = sheets("consolidation").range("o:o") if acol.cells(1, 1).value = "price_group" set myoutcol = sheets("consolidation").range("p:p") if acol.cells(1, 1).value = "sell_price" set myoutcol = sheets("consolidation").range("q:q") if acol.cells(1, 1).value = "package_weight_in_pounds" set myoutcol = sheets("consolidation").range("r:r") if acol.cells(1, 1).value = "cost" set myoutcol = sheets("consolidation").range("s:s") if acol.cells(1, 1).value = "quantity_sold" set myoutcol = sheets("consolidation").range("t:t") if acol.cells(1, 1).value = "invoice_total" set myoutcol = sheets("consolidation").range("u:u") if acol.cells(1, 1).value = "ship_to_city" set myoutcol = sheets("consolidation").range("v:v") if acol.cells(1, 1).value = "ship_to_state" set myoutcol = sheets("consolidation").range("w:w") if acol.cells(1, 1).value = "ship_to_postal_code" set myoutcol = sheets("consolidation").range("x:x") if acol.cells(1, 1).value = "vendor_number" set myoutcol = sheets("consolidation").range("y:y") if acol.cells(1, 1).value = "vendor_name" set myoutcol = sheets("consolidation").range("z:z") if not myoutcol nothing ' don't move top line, contains headers - no data set myincol = acol set myincol = myincol.offset(1, 0).resize(myincol.rows.count - 1, myincol.columns.count) ' transfer data project tab consolidated tab iloop = jloop progressbar2.value = progressbar2.min r = 1 rowmaximum c = 1 colmaximum each arow in myincol.rows myoutcol.cells(iloop, 1) = arow.cells(1, 1).value if sheet2.cells(rows.count, "a").end(xlup).row <> sheet2.cells(rows.count, "b").end(xlup).row nextrow = sheet2.cells(rows.count, "a").end(xlup).row + 1 sheet2.range("a" & nextrow) = demanddata.name end if next arow end if next acol end if if iloop > jloop jloop = iloop next demanddata sheet2.columns.autofit msgbox "consolidation of demand completed" sheet2.activate application.screenupdating = true end sub i have lines of code in think correct still won't run!!!! please, , support more appreciated!! feel free suggest other means of improving well.
thanks!!
Comments
Post a Comment