Validating data entry in Worksheet change event : Worksheet Event « Excel « VBA / Excel / Access / Word






  1. VBA / Excel / Access / Word
  2. Excel
  3. Worksheet Event

Validating data entry in Worksheet change event

 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim VRange As Range, cell As Range
    Dim Msg As String
    Dim ValidateCode As Variant
    Set VRange = Range("A1:C4")
    For Each cell In Target
        If Union(cell, VRange).Address = VRange.Address Then
            ValidateCode = EntryIsValid(cell)
            If ValidateCode = True Then
                Exit Sub
            Else
                Debug.Print "Cell " & cell.Address(False, False) & ":" & ValidateCode
                Application.EnableEvents = False
                cell.ClearContents
                cell.Activate
                Application.EnableEvents = True
            End If
        End If
    Next cell
End Sub
Function EntryIsValid(cell) As Variant
    If cell = "" Then
        EntryIsValid = True
        Exit Function
    End If

    If Not IsNumeric(cell) Then
        EntryIsValid = "Non-numeric entry."
        Exit Function
    End If

    If CInt(cell) <> cell Then
        EntryIsValid = "Integer required."
        Exit Function
    End If

    If cell < 1 Or cell > 12 Then
        EntryIsValid = "Valid values are between 1 and 12."
        Exit Function
    End If

    EntryIsValid = True
End Function








Related examples in the same category

1.Worksheet_SelectionChange(ByVal Target As Range)
2.With each range selection you make in the worksheet, the background color of the selection will turn blue.
3.Creating a Database Form From an Excel Worksheet in worksheet activate event
4.Cancel a change in Worksheet Selection Change event
5.If you want the same data to appear on other sheets but not in the same cell addresses
6.Activating Only the Used Range
7.Create a function for Excel: Is a cell formula cell
8.BeforeDoubleClick event
9.BeforeRightClick event
10.The Change event
11.Preventing data validation from being destroyed
12.changes a cell's interior color to red when it is double-clicked:
13.Worksheet_Calculate()
14.makes use of conditional formatting and overwrites any existing conditional formatting on the sheet.
15.Use a Change event to take what is in the cell and insert the colon for you
16.To validate user input, one possible location for the code is the SheetChange() event procedure of the Workbook object.
17.Use Event Parameters to cancel an event
18.disable the events and then reenable them at the end of the procedure:
19.Track worksheet change event
20.trace worksheet activate event
21.Worksheet Change Events
22.Monitoring a specific range for changes
23.checks each changed cell and displays a message box if the cell is within the desired range.
24.SelectionChange event executes whenever the user makes a new selection on the worksheet.
25.The event-handler procedure for the SelectionChange event
26.Using the Change Event to Respond to Worksheet Changes
27.Examples of Activation Events
28.Example that activates cell A1 whenever the sheet is activated:
29.Some Worksheet event are executed before the associated event occurs and have a Cancel parameter that is passed by reference.
30.Enable Events
31.event procedure runs every time the worksheet recalculates
32.Change the range color in selection change event