Msgbox loop & unable to close msgbox

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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:
Code:
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
 
Upvote 0
You are welcome.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top