Forward not Backwards

kev6264

New Member
Joined
Jun 16, 2006
Messages
44
I have this procedure that works great, but if I select a two-letter code in a cell that precedes another cell, the procedure allows the duplicate. How can I turn this into a forward and backwards checker?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    If Application.WorksheetFunction.CountIf(Range("$D$2:$D" & Target.Row), Target) > 1 Then
        MsgBox "You have chosen a duplicate sine. Please try again"
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi,

try this
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Cells(Rows.Count, 4).End(xlUp).Row

    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    If Application.WorksheetFunction.CountIf(Range("$D$2:$D" & LR), Target) > 1 Then
';;;
kind regards,
Erik
 
Upvote 0
Should I replace the entire procedure with what you created? Or just insert before the

MsgBox "You have chosen a duplicate sine. Please try again"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub
 
Upvote 0
without End Sub it's never gonna work ;)

I had just pasted the first part - which was the condition - since the rest was the same - action part
 
Upvote 0
I wouldn't know what can go wrong then some simple mistake, unless I'm missing something very obvious ...
It's working fine for me and it is not complicated at all.

what is the code you are using ???
it should read
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Cells(Rows.Count, 4).End(xlUp).Row

    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    
    If Application.WorksheetFunction.CountIf(Range("$D$2:$D" & LR), Target) > 1 Then
    MsgBox "You have chosen a duplicate sine. Please try again"
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    End If

End Sub
is it in the correct sheetmodule ?
perhaps you've still your old code in the sheetmodule and pasted the new code to the wrong sheetmodule

what popup do you get when you put this line after the line
LR = ...
Code:
MsgBox LR

best regards,
Erik
 
Upvote 0
I copied and pasted your code word for word and now it doesn't allow any two-letter combo. I inserted the MsgBox LR and it replies 2512.

Is there a way to insert my spreadsheet so you can see it happen? Thanks for being so patient Erik.

kevin
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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