Worksheet change event not working

ajoy123rocks · Nov 11th 2021

Changing to ucase is working but when i enter any score in Col F , it gives error. Screenshot attached

**royUK** · Nov 11th 2021

I've attached the example workbook that is working for me. I have no idea why it's not working for you unless the cells have been locked.

ajoy123rocks · Nov 11th 2021

i changed the part of code to the following and it worked

Code

            ActiveSheet.Unprotect Password:="1234"
            Target.Font.Color = vbRed
            ActiveSheet.Protect Password:="1234"
            Else
            
            ActiveSheet.Unprotect Password:="1234"
            Target.Font.Color = vbBlack
            ActiveSheet.Protect Password:="1234"

**royUK** · Nov 11th 2021

That more than likely means that somewhere there is a cell that is Locked.

You don't need to protect twice

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim Left_score As Integer
    Dim Right_score As Integer
    
    Application.ScreenUpdating = False
    
    If Target.Cells.count > 1 Or Target.HasFormula Then Exit Sub
    Me.Unprotect "1234"
    On Error GoTo exit_proc
    
    If Not Intersect(Target, Range("B3,A9:A34")) Is Nothing Then
        Application.EnableEvents = False
        
        Target = UCase(Target)
        Application.EnableEvents = True
    End If
    
    
    If Not Intersect(Target, Range("F9:F333")) Is Nothing Then
        Application.EnableEvents = False
        
        Left_score = Trim(Split(Target, "-")(0))
        
        Right_score = Trim(Split(Target, "-")(1))
        
        If (Left_score = 20 And Right_score = 0) Or (Left_score = 0 And Right_score = 20) Then
            Target.Font.Color = vbRed
            Else: Target.Font.Color = vbBlack
        End If
reset:
        Application.EnableEvents = True
        Me.Protect "1234"
    End If
    
    Exit Sub
exit_proc:
    MsgBox "The following error occurred: " & Err.Description
    Resume reset
    
End Sub

Display More

ajoy123rocks · Nov 11th 2021

Ok sir.. Thanks a lot for the timely help.

ajoy123rocks · Nov 11th 2021

Issue resolved

Worksheet change event not working

Participate now!

Share