Allow user to input specific text and number-need VBA code

[Thilan]

hi anyone can help me on below,

I want users to enter specific text (AB, DO, SL) and numbers (-10 to 20) in the cell range(A1:B10, A20: B30). Users should be able to enter only allowed data.
if not cell should be clear.
I don't want to use data validation impact I need VBA codes.

please help me.
Thanks.

 

[Peter_SSs]

Welcome to the MrExcel board!

numbers (-10 to 20)

It is unclear whether they must be whole numbers or not. Code below allows values like -8.62 or 19.99.

See if this Worksheet_Change event code does what you want. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim sCleared As String
 
  Set Changed = Intersect(Target, Range("A1:B10,A20:B30"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      Select Case c.Value
        Case "AB", "DO", "SL", -10 To 20
        Case Else
          sCleared = sCleared & vbLf & c.Address(0, 0) & " (" & c.Value & ")"
          c.ClearContents
      End Select
    Next c
    Application.EnableEvents = True
    If Len(sCleared) > 0 Then MsgBox "Invalid entry removed from" & sCleared
  End If
End Sub

 

[Thilan]

Welcome to the MrExcel board!


It is unclear whether they must be whole numbers or not. Code below allows values like -8.62 or 19.99.

See if this Worksheet_Change event code does what you want. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim sCleared As String
 
  Set Changed = Intersect(Target, Range("A1:B10,A20:B30"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      Select Case c.Value
        Case "AB", "DO", "SL", -10 To 20
        Case Else
          sCleared = sCleared & vbLf & c.Address(0, 0) & " (" & c.Value & ")"
          c.ClearContents
      End Select
    Next c
    Application.EnableEvents = True
    If Len(sCleared) > 0 Then MsgBox "Invalid entry removed from" & sCleared
  End If
End Sub

yes its working grate. well appreciated your effort. thank you very much.

 

[Peter_SSs]

yes its working grate. well appreciated your effort. thank you very much.

You're welcome. Thanks for the follow-up.