Process Bar

[jgresko]

I have an Excel spreadsheet that I inherited and I have fixed and added everything for my department. The spreadsheet has an update tab that runs numerous VBA subs to update the sheet after runing a query. The update is long, so there is a progress and process bar to show the user how much longer they will need to wait. The progress bar works fine, but the process bar does not move like it should. Attached are the portions of the VB code that pertains to the process bar. Please Help.

Dim progress As Integer
Dim process As Integer
Dim total As Integer

Sub updateAllData()
Application.DisplayAlerts = False
progress = 0
process = 0
Sheets("Update").ProgressBar1.Value = progress
Sheets("Update").ProgressBar2.Value = process
Sheets("Update").btnUpdateAll.Caption = "Updating"
Application.ScreenUpdating = False
updateBar1
makeMissing
updateBar1
Delete_rows_DC
Change_DC_ReturnTypeToP
Change_DC_ReturnTypeM
Change_DC_ReturnTypeU
Change_DC_ReturnTypeBlank
Delete_rows_FL
Change_FL_ReturnTypeToP
Change_FL_ReturnTypeM
Change_FL_ReturnTypeU
Change_FL_ReturnTypeBlank
Delete_rows_LE
Change_LE_ReturnTypeToP
Change_LE_ReturnTypeM
Change_LE_ReturnTypeU
Change_LE_ReturnTypeBlank
Delete_rows_PE
Change_PE_ReturnTypeToP
Change_PE_ReturnTypeM
Change_PE_ReturnTypeU
Change_PE_ReturnTypeBlank
Delete_rows_SE
Change_SE_ReturnTypeToP
Change_SE_ReturnTypeM
Change_SE_ReturnTypeU
Change_SE_ReturnTypeBlank
Delete_rows_SL
Change_SL_ReturnTypeToP
Change_SL_ReturnTypeM
Change_SL_ReturnTypeU
Change_SL_ReturnTypeBlank
Delete_rows_787
Change_787_ReturnType8
Change_787_ReturnType700
Change_787_ReturnType7011
'makeListed'
Application.ScreenUpdating = False
updateBar1
Application.ScreenUpdating = False
Workbooks.Open Filename:="R:\Delete_Yearly\Production Returns\Production_Return_Status(Updates_Go_Here).xls"
Windows("Production_Return_Status(Updates_Go_Here).xls").Activate
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess
addStatus
updateBar1
Windows("Production_Return_Status(Updates_Go_Here).xls").Activate
cleanStatus
updateBar1
Application.ScreenUpdating = False
Windows("Production_Return_Status(Updates_Go_Here).xls").Activate
        ActiveWorkbook.SaveAs Filename:= _
        "R:\Delete_Yearly\Production Returns\Production_Return_Status(Updates_Go_Here).xls", AccessMode:= _
        xlShared
ActiveWorkbook.Close (False)
Workbooks("Production_Returns_Main.xls").Activate
makeChartData
updateBar1
updateBar2
Sheets("Update").btnUpdateAll.Caption = "Run Update"
Sheets("Update").ProgressBar1.Value = 10
Application.ScreenUpdating = True
End Sub

Sub updateBar1()
Windows("Production_Returns_Main.xls").Activate
Sheets("Update").Select
Application.ScreenUpdating = True
Sheets("Update").ProgressBar1.Value = progress
progress = progress + 2
Application.ScreenUpdating = False
End Sub

Sub updateBar2()
Windows("Production_Returns_Main.xls").Activate
Sheets("Update").Select
Application.ScreenUpdating = True
Sheets("Update").ProgressBar2.Value = progress
process = process + 2
Application.ScreenUpdating = False
End Sub

 

[tweedle]

is this

Sub updateBar2()
Windows("Production_Returns_Main.xls").Activate
Sheets("Update").Select
Application.ScreenUpdating = True
Sheets("Update").ProgressBar2.Value = progress
process = process + 2
Application.ScreenUpdating = False
End Sub

suppose to be

Sub updateBar2()
Windows("Production_Returns_Main.xls").Activate
Sheets("Update").Select
Application.ScreenUpdating = True
Sheets("Update").ProgressBar2.Value = [COLOR=blue]process ' progress[/COLOR]
process = process + 2
Application.ScreenUpdating = False
End Sub

 

[jgresko]

I looked at this for hours and did not see my mistake caused by cut and paste. Thanks!!

 

[tweedle]

N/P:

CTRL+H is your friend