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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

kev6264

New Member
Joined
Jun 16, 2006
Messages
44

ADVERTISEMENT

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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

kev6264

New Member
Joined
Jun 16, 2006
Messages
44

ADVERTISEMENT

Erik

I pasted it and it still only checks up not down. Please excuse my ignorance..
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

kev6264

New Member
Joined
Jun 16, 2006
Messages
44
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
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

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