Issue with freezing screen and userform

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394
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.

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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
Usually (Not Responding) means excel is running a very long code(loop), or possibly an infinate loop.

I'm curious to know, if you have screen updating as false, would the userform show the progress?
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,766
Using find is slow. See if this helps speed things up. HTH. Dave
Code:
Function RawATNr(mrow As Long)
Dim MAINws As Worksheet, RAWws As Worksheet
Dim lrow As Long
Dim ATN As Variant, BigRng As Range, Rng As Range
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
Set BigRng = RAWws.Range("H2:H" & lrow)
For Each Rng In BigRng
If InStr(LCase(CStr((Rng))), LCase(CStr(MAINws.Cells(mrow, "C")))) Then
RawATNr = Rng.Row
Exit Function
End If
Next Rng
End Function
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,766
Finished my coffee and had a re-think. This is better. Dave
Code:
Function RawATNr(mrow As Long)
Dim MAINws As Worksheet, RAWws As Worksheet
Dim lrow As Long
Dim ATN As Variant, Arr As Variant, Cnt As Integer
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
Arr = RAWws.Range("H2:H" & lrow).Value
For Cnt = LBound(Arr) To UBound(Arr)
If LCase(Arr(Cnt, 1)) = LCase(MAINws.Cells(mrow, "C")) Then
RawATNr = Cnt + 1
Exit Function
End If
Next Cnt
End Function
 
Solution

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394

ADVERTISEMENT

Hello NdNoviceHlp,

I used your code 2nd code as you noted it and tried to do steps to see if the code works. Once i entered the function and when the the line "For Cnt = Lbound..." it gave me an overflow error. Don't know what i did wrong.
 

zoog25

Active Member
Joined
Nov 21, 2011
Messages
394
Ok so i think i was able to research and fix the second entry code. If you Change "Cnt as Integer" to "Cnt as Long" then it works. I think it might be because in this particular list i'm going through, there are around 70,000 + entries so it might have been outside the realm of what Integer can hold. Thank you. I'm marking this as solved for now.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,348
I'm marking this as solved for now.

@zoog25, glad you got the solution.

About marking a post as a solution: we should mark the actual post that contains the solution code to help the future readers better, which is @NdNoviceHlp's post in this thread as you also confirmed. So I have switched the marked solution post.

Regarding the variable type which is a specific and data-based detail in this question, it surely requires the Long type since the Int type can only hold values between -32768 to +32767 and you have 70K rows.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,354
Messages
5,624,195
Members
416,017
Latest member
moritz210

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top