Disable macro for keying in data in range of cells

Hello guys,

I have created a table with list of scholar's ID and name in column A and B respectively, name of class subjects which the scholar are taking in column E to K, Pre-test columns for all subjects in column L to CL and Post-test columns for all subjects in column CM to FM. I have used this VBA code which will only filter the scholar names and subject taken based on the dropdown (created using data validation) selected in cell B2, and hide any other rows/columns. This code actually works perfectly for this purpose:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:=""
    Sheet1.Hide_Columns_Containing_Value
    ActiveSheet.Range("$D$6").AutoFilter Field:=4, Criteria1:=Range("$B$2").Value
    ActiveSheet.Protect Password:=""
    Application.ScreenUpdating = True
End Sub


Sub Hide_Columns_Containing_Value()
Dim c As Range
    For Each c In Range("E3:FM3").Cells
        If c.Value = 0 Then
            c.EntireColumn.Hidden = True
        End If
        If c.Value = 1 Then
            c.EntireColumn.Hidden = False
        End If
    Next c
End Sub

However, every time my data entry staff keying in data in the pre/post test columns (column L to FM), there's a slight delay after pressing ENTER or TAB to the next cell. Keying in several data will result in noticeable sluggishness. I understand that this is most likely due to the nature of the Worksheet_Change function which will run whenever there's any change in any cell. I've read and find out that to temporarily disable this macro, we can use the Application.EnableEvents function. But unfortunately I do not really know to use this function, so I would like to seek your kind help on this.

In short, I would like to disable macro whatsover whenever any data is keyed in in the pre/post test columns which will help reduce the sluggishness. Only just enable just for the cell B2 selection dropdown changes, which will reveal/hide columns/rows based on that selection. For your information, the whole range of the pre/post test columns is L7:FM132.

Thank you for any kind help.

-Shahiran

Re: Disable macro for keying in data in range of cells

Hello Mr Carim,

Your code works very well. I can feel that keying in data is a bit faster now.

Thank you very much!