More Descriptive Message Boxes

Kirnon

Board Regular
Joined
Apr 23, 2008
Messages
110
I need a message box which pops up when there is duplication on a spreadsheet, to be more descriptive by showing some of the duplicated information in the message. I have entered stuff but due to the order in which I am doing things I can't check if it works and since all my VBA is self taught I struggle to eyeball stuff. Can a much more learned forumite give this an eyeball and let me know if it will work and if not what I need to change?

Many thanks.

Code:
Sub New_Applicant()


Dim shp As Shape
Dim Code As String
Dim c As Range


NewRow = Sheets("ERVS").Cells(Rows.Count, 1).End(xlUp).Row + 1
Set Rng = Sheets("ERVS").Range("A" & NewRow)


Application.ScreenUpdating = False


With Sheets("ERVS").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row + 1)
    Set c = .Find(UserForm1.TextBox1.Value, LookIn:=xlValues)
    If Not c Is Nothing Then
            ans = MsgBox(Cells(Cell.Row, 11).Value & " " & Cells(Cell.Row, 12).Value & "'s post " & Cells(Cell.Row, 14).Value & _
            "is already on the sheet, the applications progress is " & Cells(Cell.Row, 4).Value & ". Do you want to continue?", vbYesNo + vbCritical, "Caution")
        Select Case ans
            Case 6 'Yes
                Application.Run "CopyStuff"
            Exit Sub
            Case 7 'No
            Exit Sub
        End Select
    End If
End With


Application.Run "CopyStuff"


Application.ScreenUpdating = True


End Sub
 

Robertvk

Board Regular
Joined
Oct 15, 2015
Messages
121
To be clear;
You want to check changed cells in (whole worksheet, column, row?), to see if it is a duplicate value?

And if that is true, you want to tell the user what exactly?
 

Kirnon

Board Regular
Joined
Apr 23, 2008
Messages
110
Hi Robertvk,

Thank you for responding.

This is checking a value on a userform prior to entry against the used range on a spreadsheet to see if the information is already there. The message is meant to pull information from the row that holds the potentially duplicated values and display it as part of the messagebox text.

I hope that is clear.

Kirnon
 

Robertvk

Board Regular
Joined
Oct 15, 2015
Messages
121
I would approach it something like this:

Code:
Sub dupfinder()

Dim ws As Worksheet
Dim finder As Range
Dim ans As Integer

Set ws = ActiveWorkbook.Worksheets("Sheet1")

If WorksheetFunction.CountIf(ws.Range("A:A"), UserForm1.TextBox1.Value) > 1 Then
Set finder = ws.Range("A:A").Find(UserForm1.TextBox1.Value, , xlFormulas, xlWhole, xlByRows, xlNext, False)
    If Not finder Is Nothing Then
    ans = MsgBox("Duplicate value detected!" & vbNewLine & vbNewLine & "Duplicate value found in: " & finder.Address, vbYesNo, "caution")
    Else: End If
Else: End If

End Sub
This code counts the instances of UserForm1.TextBox1.Value in column A. If there are more than 2 values (duplicates) then it will search for the first value that matches UserForm1.TextBox1.Value.

This is perhaps the first step in what you want to achieve
 

Kirnon

Board Regular
Joined
Apr 23, 2008
Messages
110
Okay,

Thanks, it's better than nothing - my previous message only stated that there was a duplicate at least this points to the location.

Thanks for your help Robertvk
 

Kirnon

Board Regular
Joined
Apr 23, 2008
Messages
110
Is it possible to display the value of a cell which is offset 3 to the right of the finder.address?
 

Robertvk

Board Regular
Joined
Oct 15, 2015
Messages
121
I'm on mobile so I can't test it, but it's probably something like:

Finder.Offset(0,3).address
 

Kirnon

Board Regular
Joined
Apr 23, 2008
Messages
110
Spot on Mate, only needed to change .address with .value and it worked a treat.

Your help has been great.

Many thanks
 

Forum statistics

Threads
1,082,258
Messages
5,364,086
Members
400,779
Latest member
lumers

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top