Code lags for a few seconds before computing

[DavideXL]

Hi there
I have created the code below finding bits and pieces from this forum. I have done coding before but I am relatively new to VBA. Code was working relatively fine until I have introduced this section.

If .Range("Volt").Value = "DC" Then
            .Range("pf").Formula = "1"  
End If

What I want to achieve is to have a fixed value (1) when "DC" is selected, and manual entry the value when is not. Code delivers the output but looks like computations are overloaded as it takes around 5s for Excel to revive from the trauma of reading the code . Any advice on why is this happening and how to improve the above section or the code in general. Any answers will be much appreciated. Cheers

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    
    With Sheets("Input & Results")
        
        '-----Change visibility of check boxes--------------'
        
        If .Range("CorTyp").Value = "Multicore" Then
            .CheckBoxes("CheckBoxSc").Visible = True
        Else
            .CheckBoxes("CheckBoxSc").Visible = False
        End If
        
        If (.Range("CorTyp").Value = "Multicore") Or (.Range("InsCon").Value = "Unenclosed - Spaced from surface") Or (.Range("Volt").Value = "DC") Then
            .CheckBoxes("CheckBoxSpa").Visible = False
            .CheckBoxes("CheckBoxSpa").Value = False
        Else
            .CheckBoxes("CheckBoxSpa").Visible = True
        End If
        
        If .Range("CorMat").Value = "Al" Then
            .CheckBoxes("CheckBoxTC").Visible = False
            .CheckBoxes("CheckBoxTC").Value = False
        Else
            .CheckBoxes("CheckBoxTC").Visible = True
            '.CheckBoxes("CheckBoxTC").Value = True
        End If
        
        '-------Assign a fix value to pf cell in when Voltage is DC-----------'
        
        [B]If .Range("Volt").Value = "DC" Then
            .Range("pf").Formula = "1"
        End If[/B]
        
        '-------Clear cells if dropdown values are changed-----------'
        
        If Not Application.Intersect(Target, Range("Volt")) Is Nothing Then
            If Target.Validation.Type = 3 Then
                Application.EnableEvents = False
                .Range("SupVol").ClearContents
                .Range("ConCon").ClearContents
            End If
        End If
        
        If Not Application.Intersect(Target, Range("CorTyp")) Is Nothing Then
            If Target.Validation.Type = 3 Then
                Application.EnableEvents = False
                .Range("InsCon").ClearContents
                .Range("ArrCir").ClearContents
                .Range("ConCon").ClearContents
            End If
        End If
        
        If Not Application.Intersect(Target, Range("InsCon")) Is Nothing Then
            If Target.Validation.Type = 3 Then
                Application.EnableEvents = False
                .Range("ArrCir").ClearContents
                .Range("NoC").ClearContents
            End If
        End If
        
        If Not Application.Intersect(Target, Range("ArrCir")) Is Nothing Then
            If Target.Validation.Type = 3 Then
                Application.EnableEvents = False
                .Range("NoC").ClearContents
            End If
        End If
        
exitHandler:
        Application.EnableEvents = True
        Exit Sub
End Sub        
    End With

 

[FormR]

Hi, it's not clear in which sheet this code resides, but you probably want to disable events with Application.EnableEvents = False before you make that change to the sheet (like you have done elsewhere in the code) to prevent the change event firing itself.

If it were me, I'd consider disabling events only once before any potential sheet changes occur and re-enabling, like you have already done, in the exithandler.

 

[DavideXL]

Hi, it's not clear in which sheet this code resides, but you probably want to disable events with Application.EnableEvents = False before you make that change to the sheet (like you have done elsewhere in the code) to prevent the change event firing itself.

If it were me, I'd consider disabling events only once before any potential sheet changes occur and re-enabling, like you have already done, in the exithandler.

Thanks @FormR, the code now works smoothly. This is what happens when u have a piece of code there and you don't even know what it was doing.