Msgbox loop & unable to close msgbox

[ipbr21054]

Hi,
Can you advise please what ive missed in the code below.

I run the code.
I see the msg box with the oK button.
Pressing OK just does nothing & im unable to close the msgbox.

Option Explicit
Private Sub DatabaseCustomerDuplicateSearch_Click()


Dim Cell As Variant
Dim Source As Range


Set Source = Range("A6:A5000")


For Each Cell In Source
    
    If Application.WorksheetFunction.CountIf(Source, Cell) > 1 Then
        
        Cell.Interior.Color = RGB(0, 255, 0)
    Else
    MsgBox " NO DUPLICATES WERE FOUND ", vbExclamation, "DATABASE DUPLICATE CHECKER"
    End If
Next


End Sub

 

[Joe4]

Since you put the MsgBox within your loop, and you are looping throrugh 4995 times, you would get that message box pop up up to 4995 times (if there no duplicates found)!
So I think what is happening is the next one is popping up as fast as you are closing them.

I think you want to wait, and only post the MsgBox after you are done looping through all cells. So you want move it outside the loop, something like this:

Private Sub DatabaseCustomerDuplicateSearch_Click()

    Dim Cell As Variant
    Dim Source As Range
    Dim dups As Long

    Set Source = Range("A6:A5000")

    For Each Cell In Source
        If Application.WorksheetFunction.CountIf(Source, Cell) > 1 Then
            Cell.Interior.Color = RGB(0, 255, 0)
            dups = dups + 1
        End If
    Next Cell
    
    If dups > 0 Then
        MsgBox " DUPLICATES WERE FOUND AND HAVE BEEN HIGHLIGHTED ", vbExclamation, "DATABASE DUPLICATE CHECKER"
    Else
        MsgBox " NO DUPLICATES WERE FOUND ", vbExclamation, "DATABASE DUPLICATE CHECKER"
    End If

End Sub

 

[ipbr21054]

That it,

many thanks

 

[Joe4]

You are welcome.