CODEWITHSUNDEEP
excelvbaworksheet
user21040246
user21040246

Reputation: 9

combine 2 worksheet_change

what am I doing wrong here...please help, thanks PG

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ValidatedCells As Range
Dim ValidatedCells2 As Range
    Dim Cell As Range
    Set ValidatedCells = Intersect(Target, Target.Parent.Range("G:G"))
    Set ValidatedCells2 = Intersect(Target, Target.Parent.Range("H:H"))
    
    Application.EnableEvents = False
    
    If Not ValidatedCells Is Nothing Or Not ValidatedCells2 Is Nothing Then
        For Each Cell In ValidatedCells
            If Not Len(Cell.Value) <= 20 Then
                MsgBox "The Name """ & Cell.Value & _
                """ inserted in " & Cell.Address & _
                " in column G was longer than 20. Undo!", vbCritical
                Application.Undo
            End If
        Next Cell
        For Each Cell In ValidatedCells2
            If Not Len(Cell.Value) <= 50 Then
                MsgBox "The Name """ & Cell.Value & _
                """ inserted in " & Cell.Address & _
                " in column H was longer than 50. Undo!", vbCritical
                Application.Undo
                                
        Next Cell
               Exit Sub
            End If
  
    Application.EnableEvents = True
   
    
End Sub

I tried above and not sure if it is the syntax or if the loop statements are incorrect, please help

Upvotes: 0

Views: 39

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

A few issues in your code - after calling Undo there's no point in continuing, so you can just exit at that point. Needs some error handling to make sure Events are not left turned off.

I'd maybe do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error GoTo done
    If TooLong(Intersect(Target, Me.Range("G:G")), 20) Then GoTo done
    If TooLong(Intersect(Target, Me.Range("H:H")), 50) Then GoTo done
    
done:
    If Err.Number <> 0 Then MsgBox Err.Description 'in case of error
    Application.EnableEvents = True
End Sub

'If any cell in range `Monitored` has content longer than `maxLen`, 
'   call Undo and return True
Function TooLong(Monitored As Range, maxLen As Long) As Boolean
    Dim c As Range
    If Not Monitored Is Nothing Then
        For Each c In Monitored.Cells
            If Len(c.Value) > maxLen Then
                MsgBox "The Name """ & c.Value & """ inserted in " & c.Address & _
                    " in column was longer than " & maxLen & ". Undo!", vbCritical
                Application.EnableEvents = False
                Application.Undo
                TooLong = True
                Exit Function
            End If
        Next c
    End If
End Function

Note: in a worksheet code module you can use Me to refer to the worksheet, instead of Target.Parent

Upvotes: 1

Related Questions