Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005

    Unanswered: Triggering code on tabulator

    I'm using the below code to check for uniqueness in a column, when someone enters a value in a cell in column D.

    If the user enters a value and hits ENTER, the code runs correctly.

    If the user hits tab, to change the value in the adjacent cell, the code doesn't run at all.

    As most users here usually tab through the sheet, to change all required data in a row, my code is practically worthless

    Is there a way to trigger the code on tab? I've tried Worksheet_SelectionChange as well, same problem. If the user just tabs along, it doesn't run the code.

    Thanks in advance,


    Private Sub Worksheet_Change(ByVal Target As Range)
    '   Lavet den 24 maj 2012 af KET
    '   Bruges til at sikre unikke ISRC koder.
        'MsgBox "yadda: " & ActiveCell.Column, vbCritical
        If ActiveCell.Column = 4 Then ' er vi i kolonnen med ISRC?
            MsgBox "Tjekker om koden er brugt tidligere", vbOKOnly
            Dim LLoop As Integer
            Dim LTestLoop As Integer
            Dim Lrows As Integer
            Dim LRange As String
            Dim LChangedValue As String
            Dim LTestValue As String
            Lrows = 10000 ' antal rækker der skal tjekkes
            LLoop = 2 ' vi vil ikke have overskriften med.
            'tjek rækkerne i excelarket. Antal rækker der skal tjekkes er i variabel Lrows
            While LLoop <= Lrows
                LChangedValue = "D" & CStr(LLoop)
                If Not Intersect(Range(LChangedValue), Target) Is Nothing Then
                    If Len(Range(LChangedValue).Value) > 0 Then
                        'Test alle værdier for unikhed (unikhed? er der noget der hedder det)
                        LTestLoop = 2
                        While LTestLoop <= Lrows
                            If LLoop <> LTestLoop Then
                                LTestValue = "D" & CStr(LTestLoop)
                                'Value has been duplicated in another cell
                                If Range(LChangedValue).Value = Range(LTestValue).Value Then
                                    'Set the background color to red
                                    Range(LChangedValue).Interior.ColorIndex = 3
                                    MsgBox Range(LChangedValue).Value & " Eksisterer allerede i celle D" & LTestLoop
                                    Exit Sub
                                    Range(LChangedValue).Interior.ColorIndex = xlNone
                                End If
                            End If
                            LTestLoop = LTestLoop + 1
                    End If
                End If
                LLoop = LLoop + 1
        End If
    End Sub
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    May 2012
        If Target.Column = 4 Then ' er vi i kolonnen med ISRC?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts