PopUp Message Box for Duplicates on worksheet

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi
I would like some help to create a popup message box that would be displayed in another worksheet in a work book when on duplicates be produce in any of 2 other worksheets of data where duplicates of colour sequence has occurred. Below is a snapshot which I hope will explain what it is I am looking for help on
Cheers
NZAS

1604004695800.png
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have found some Message Box VBA code that does work if the assigned button is on that worksheet but I want to place the button on a different worksheet (colour Chart)
If I could get the VBA to work if the button used to run the code is placed in a different work sheet. What I would like is to be able to have the button the the VBA would be assigned to is on Colour Chart work sheet but activate the checking for Duplicates on The worksheet is called Bulk Shipment.
I do not know how this would be done in the VBA code
Cheers
NZAS

VBA Code below
Sub Show_Duplicates_Bulk()
Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each c In r
If WorksheetFunction.CountIf(r, c) > 1 Then If InStr(1, s, c) = 0 Then s = s & "," & c
Next
MsgBox IIf(s <> " ", "Found Duplicates" & vbLf & Mid(s, 2), "No dup")
End Sub
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,965
All of the following is presuming your macro does in fact find duplicates.

VBA Code:
Sub Show_Duplicates_Bulk()
Set r = Sheets("Bulk Shipment").Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each c In r
If WorksheetFunction.CountIf(r, c) > 1 Then If InStr(1, s, c) = 0 Then s = s & "," & c
Next
MsgBox IIf(s <> " ", "Found Duplicates" & vbLf & Mid(s, 2), "No dup")
End Sub

Rather than use the sheet name, it would be best to use the sheet reference number. Example : When looking in the VBE window, your sheet "Bulk Shipment" may show
as Sheet1("Bulk Shipment") . So in this example the line of code would be : Set r = Sheet1.Range("A1", Range("A" & Rows.Count).End(xlUp))
 

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

I have got a copy of my file what was the address I can send it to
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,965
Disregard sending the workbook. Download and review the example workbook in Post #4
 

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Further to this.
I have progressed a bit further through searching but still need further help now.
Need to improve the Macro duplicates test2 so that all duplicated colours along with the orders all get display currently v2 only display one of the duplicates what I need to get is a list of all the colours that have been duplicated in the data and each order number that is associated to that colour. The macros return the Message box to another work sheet which will not change in the macros. Macros also attached. There are 2 version which I have used
See attachments
 

Attachments

  • Colours Data test.jpg
    Colours Data test.jpg
    199.3 KB · Views: 6
  • Colours Data test2 .jpg
    Colours Data test2 .jpg
    117.6 KB · Views: 6
  • Macros for Message box.jpg
    Macros for Message box.jpg
    171.5 KB · Views: 7

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I may be confusing things with this. Below is the code I use that displays there are Duplicates in column B and it returns what is the duplicates.
What I am looking for is the detail of the duplicate in column be and also the value in column C next to the duplicate. (See may comment also above on 20/11/20)
the code
Sub Show_Duplicates_Other()
Sheets("Other Shipments").Select
Range("A4").Select
Set r = Range("B1", Range("B" & Rows.Count).End(xlUp))
For Each c In r
If WorksheetFunction.CountIf(r, c) > 1 Then If InStr(1, s, c) = 0 Then s = s & "," & c
Next
MsgBox IIf(s <> " ", "Duplicates Found" & vbLf & Mid(s, 2), "No duplicates")
End Sub

My example message box would be if there are duplicates in column B
Column B has duplicates of 2BLA / BLU in 2 rows in column C in rows beside the duplicates the values are different 8020123 and 8020124

The display in the message box would display the value for the Duplicate and the value in Column "C"
BLA / BLU 8020123, 8020124 and if others would also display those
Cheers
NZAS
 

Watch MrExcel Video

Forum statistics

Threads
1,127,605
Messages
5,625,762
Members
416,136
Latest member
senthil_sk

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