In this project we will create a procedure that will demonstrate the concept of using 'events' to prevent duplicate entries in a column.
NOTE:- The target part of the code that changes the font color to blue is not neccessary, but is included in the code to demonstrate how the target reference to an 'object' can be used.
- The column to be sorted is set to column 'A'.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim currentCell, nextCell
Target.Font.ColorIndex = 5
If ActiveWindow.RangeSelection.Cells.Count = 1 Then
Worksheets("Sheet1").Range("A1").Sort key1:=Worksheets("Sheet1").Range("A1")
Set currentCell = Worksheets("Sheet1").Range("A1")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 0)
If nextCell.Value = currentCell.Value Then currentCell.Delete
Set currentCell = nextCell
Loop
End If
End Sub
EXPLANATION:
- To prevent errors if the whole column is selected and the contents deleted, the procedure first checks to see if the selection is only one cell by checking the value of the 'ActiveWindow.RangeSelection.Cells.Count' property. If it's value is one the procedure continues.
- The next task is to sort the values in the column so that the following loop can compare values in adjacent cells.
Worksheets("Sheet1").Range("A1").Sort key1:=Worksheets("Sheet1").Range("A1")
Although it has not been included in this code example, in a real coding the workbook would be resorted 'back' using the values in a key column.
- After first setting the currentCell to 'A1', the next task is to set the loop to check for duplicate values. The procedure continues to loop while the current cell is not empty using the 'IsEmpty' property value.
Do While Not IsEmpty(currentCell)
- The main part of the code is the next line that checks the value of the next cell in the column to the current cell using the 'Offset(RowOffset, ColumnOffset)' property value. If they are equal, then the current cell is deleted.
If nextCell.Value = currentCell.Value Then currentCell.Delete
Alternatively or in addition, a message box could popup to tell the user that duplicate data has just been entered.
- FINALLY, the last part of the loop sets the current cell to the next cell to continue the loop.
- THATS IT! You could practice using events by altering the code so that it just loops through the values in a column and gives the user a warning if they have entered a duplicate value.(Remember to resort the worksheet using your key value at the end of the procedure)
YOU HAVE NOW LEARNT HOW EASY IT IS TO USE EVENTS TO AUTOMATICALLY DO SOME ACTION(S) IN YOUR WORKSHEET.
|