Hello All,
I'm having an issue with a program I'm trying to code that involves using a userform to show a progress bar. I found instructions online on how to add a progress bar userform and I did my best to incorporate the code with what i was doing. The problem is that the coding i use process around 75,000 rows on an excel spreadsheet and when i run the code. Excel pops up the userform progress bar but everything is blank and i get the (Not Responding). Can someone please look at my coding and let me know where i went wrong. Thank you.
Sorry about the long line of coding. I'm trying to show the order in which the coding process. You have the first userform which has just several buttons. Each button activates code to do a task. For this one particular series, the main coding compares two different reports and compares is the new report removed any items from the old report. Next set of code is what i saw for accessing the progress bar for the programming, then the main code with it's own search function and then lastly the coding section for the progress bar to reflect how much the program has progressed. Thank you again for any help that you can provide.
I'm having an issue with a program I'm trying to code that involves using a userform to show a progress bar. I found instructions online on how to add a progress bar userform and I did my best to incorporate the code with what i was doing. The problem is that the coding i use process around 75,000 rows on an excel spreadsheet and when i run the code. Excel pops up the userform progress bar but everything is blank and i get the (Not Responding). Can someone please look at my coding and let me know where i went wrong. Thank you.
VBA Code:
Private Sub cmdVE_Click()
UF_CP.Hide
UF_ProgressEtoN.Show
MsgBox ("Done")
UF_CP.Show
End Sub
Private Sub UserForm_Activate()
CompEtoN
End Sub
Sub CompEtoN()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Declare Worksheet Variables
Dim MAINws As Worksheet, RAWws As Worksheet
'Declare Other Variables
Dim lrowMAIN As Long
Dim lrowRAW As Long
Dim drow As Long
Dim mrow As Long
Dim pcb As Long
Dim pc As Long
Dim pctCompl As Single
'Set Worksheet Variables
Set MAINws = ThisWorkbook.Worksheets("Assessments") 'Previous Assessment District Worksheet
Set RAWws = ThisWorkbook.Worksheets("Raw Data") 'New GIS Assessment District Informaiton
lrowMAIN = MAINws.Cells(Rows.Count, "C").End(xlUp).Row
lrowRAW = RAWws.Cells(Rows.Count, "H").End(xlUp).Row
UF_ProgressEtoN.Text.Caption = "0% Complete"
pcb = lrowMAIN / 100
For mrow = 2 To lrowMAIN
drow = RawATNr(mrow)
Select Case drow
Case Empty
MAINws.Cells(mrow, "Y") = "Removed" 'New/Removed Entry
End Select
Select Case mrow = pcb
Case True
pctCompl = (mrow - 1) / (lrowMAIN - 1)
progressEtoN (pctCompl)
pc = pcb + pcb
Case False
Select Case mrow = pc
Case True
pctCompl = (mrow - 1) / (lrowMAIN - 1)
progressEtoN (pctCompl)
pc = pcb + pcb
End Select
End Select
Next mrow
UF_ProgressEtoN.Hide
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Function RawATNr(mrow As Long)
Dim MAINws As Worksheet, RAWws As Worksheet
Dim lrow As Long
Dim ATN As Variant
Set MAINws = ThisWorkbook.Worksheets("Assessments") 'Previous Assessment District Worksheet
Set RAWws = ThisWorkbook.Worksheets("Raw Data") 'New GIS Assessment District Informaiton
lrow = RAWws.Cells(Rows.Count, "H").End(xlUp).Row
With RAWws.Range("H2:H" & lrow)
Set ATN = .Find(MAINws.Cells(mrow, "C"), LookIn:=xlValues)
If ATN Is Nothing Then
GoTo DoneFinding
Else
RawATNr = ATN.Row
End If
End With
DoneFinding:
End Function
Sub progressEtoN(pctCompl As Single)
UF_ProgressEtoN.Text.Caption = FormatPercent(pctCompl) & " Completed"
UF_ProgressEtoN.Bar.Width = pctCompl * 204
End Sub
Sorry about the long line of coding. I'm trying to show the order in which the coding process. You have the first userform which has just several buttons. Each button activates code to do a task. For this one particular series, the main coding compares two different reports and compares is the new report removed any items from the old report. Next set of code is what i saw for accessing the progress bar for the programming, then the main code with it's own search function and then lastly the coding section for the progress bar to reflect how much the program has progressed. Thank you again for any help that you can provide.