Excel-Enforce Unique Values

[grnzbra]

Do the cells on Excell worksheets have events ie. OnEnter, OnChange, OnExit etc? I have to create a worksheet in which users enter names of things along with various information about the named items. The Names column is the key field. When the user enters a name in that field, I need to check the entire column to be sure the name doesn't already exist and prevent the user from exiting the cell (or row) if a match is found.

When I go into the macro help and look for On Exit, I do get a write up on the following

Private Sub object_Exit( ByVal Cancel As MSForms.ReturnBoolean)

I am trying to use the named range for that column as the object.
However, when I try to go past AS within the parenthasis, all the available choices that start with MS are followed by o. There are no MSForms available in the drop down selection. I have not been able to figure out how to make this work. I'm am using Excel 2007. How can I trigger a sub when the use exits the cell?

(I thought I found a solution by creating a hidden column with the COUNTIF function and then setting the data varfication for my named range to requiring a value of <2 int the corresponding cell of the hidden column. This works if the user uses TAB or ENTER to exit the cell, but if he clicks on another cell with the mouse it doesn't work. Furthermore, if the user mouses out and then goes back to the offending cell and tries to get out using TAB or ENTER he is able to exit without triggering the data validation violation.)

 

[SkipVought]

hi,

So as I explained in your previous post, you can use the worksheet's Change event and SelectionChange event to accomplish this.

First use the SelectionChange event to store the value in the column of interest.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

BTW, this code stub is built-in the code sheet for the Worksheet. Right-click the sheet tab and select View Code. Just above and to the left of the Code Sheet is the Object Drop Down that contains (General). Select Worksheet. Next to that Drop Down is the Procedure Drop Down that contains all the Worksheet events.

So the SelectionChange event will be used to store the value in the Name column the the user may change. It could be stored in a Public variable or it could be stored on in a cell on this sheet. I'm choosing the latter.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row = 1 Then Exit Sub 'row 1 is headings
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        If Target.Count > 1 Then
[highlight #FCE94F]            'what do you want to happen when multiple cells
            'are selected including a Name?
[/highlight]        Else
            'okay only one cell is selected
            Cells(1, "XFD").Value = Target.Value
        End If
    End If
End Sub

Now the Change event where if the entered name appears more than once, it is replaced by the stored name.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Then Exit Sub 'row 1 is headings
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        If Target.Count > 1 Then
            'what do you want to happen when multiple cells
            'are selected including a Name?
        Else
            'okan only one cell is selected
            If Application.CountIf(Columns(1), Target.Value) > 1 Then
                Application.EnableEvents = False
                Target.Value = Cells(1, "XFD").Value
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub