Procedure too large on worksheet_change event

[samjones833]

Hello all,

I have written a worksheet change for each individual cell in a range. When the specific cell is changed it then runs a number of clear contents in a module, for example cell M47 value has changed by selecting a different value in the drop down, this will then clear cells N47:V47. If cell N47 is changed it will then clear cells O47:47 but keep the value in cell M47. The problem i am now getting is that the error message procedure is too large. Below is a snippet of the code, any suggestions on how i could rewrite this would much appreciated.

Private Sub Worksheet_Change(ByVal target As Range)

Application_Disable




' Main pillars fixed half bolster

    If Not Intersect(target, Range("M47")) Is Nothing Then

        Worksheet_SelectionChangeCentreDrillDirectionPillarsFixedBol

    End If

    If Not Intersect(target, Range("N47")) Is Nothing Then

        Worksheet_SelectionChangeCentreDrillTolerancePillarsFixedBol

    End If

    If Not Intersect(target, Range("O47")) Is Nothing Then

        Worksheet_SelectionChangeCentreDrillTypePillarsFixedBol

    End If

    If Not Intersect(target, Range("R47")) Is Nothing Then

        Worksheet_SelectionChangeCentreDrillMaterialPillarsFixedBol

    End If

    If Not Intersect(target, Range("S47")) Is Nothing Then

        Worksheet_SelectionChangeCentreDrillCoatingPillarsFixedBol

    End If

     If Not Intersect(target, Range("V47")) Is Nothing Then

        Worksheet_SelectionChangeDrillDirectionMainPillarBolster

    End If

Application_Enable

End sub

 

[RoryA]

Welcome to the forum.

I can't see, based on what you describe, why you need a separate macro for each cell, since they all sound like they do much the same thing.

Given the nature of the error message, there must be a lot more code involved - how many cells are you monitoring?

 

[samjones833]

Welcome to the forum.

I can't see, based on what you describe, why you need a separate macro for each cell, since they all sound like they do much the same thing.

Given the nature of the error message, there must be a lot more code involved - how many cells are you monitoring?

Hi,

Yes the module that is activated by the cell value change will simply clear the contents of the cells in front of it. For example M47's value is changed from a drop down, the cells infront of it will then clear to give users a blank row to input new details.

There are 1,020 cells that are required for this, currently each with a seprerate line of code.

 

[Fluff]

Is there anything in the columns after col V that needs to be kept?

 

[samjones833]

Is there anything in the columns after col V that needs to be kept?

Hi, yes columns W:AB contain formulas

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("M47:V100")) Is Nothing Then
      Application.EnableEvents = False
      Range(Target.Offset(, 1), Range("V" & Target.Row)).ClearContents
      Application.EnableEvents = True
   End If
End Sub

Change the V100 to what ever the last row should be

 

[samjones833]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("M47:V100")) Is Nothing Then
      Application.EnableEvents = False
      Range(Target.Offset(, 1), Range("V" & Target.Row)).ClearContents
      Application.EnableEvents = True
   End If
End Sub

Change the V100 to what ever the last row should be

That works perfectly! Thankyou

 

[Fluff]

You're welcome & thanks for the feedback.