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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Is it possible to display the value of a cell which is offset 3 to the right of the finder.address?
 
Upvote 0
I'm on mobile so I can't test it, but it's probably something like:

Finder.Offset(0,3).address
 
Upvote 0
Spot on Mate, only needed to change .address with .value and it worked a treat.

Your help has been great.

Many thanks
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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