VBA Codes clashing with one another

[alisoncleverly]

Hi,

Here is my sample file.

Can you please advise how I can fix the following codes so that they do not class with each anymore?

A snapshot on what the code should have done (but it couldn't):
1) If "Shipped" in column AU and no "x" in column AX, then return "Rollup" for all empty Sales and Production cells

2) If "Title Transfer" in the last Sales column and no "Shipped" in column AU, return "x" in column AX

3) If "Title Transfer" in the last Sales column (has to happen first) and "Shipped" in column AU (happens later), return "x" in column AX and "Shipped" in all empty Sales and Production cells (this, I haven't figured out how to)

So, I have this part to return "Rollup" for any empty Sales and Production cells if "Shipped" in Column AU (47th column).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim counter As Long
Dim lastcolumn As Long

lastColumn = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column

'Shipped without Title Transfer
      If Me.Cells(1, Target.Column).Value = "MB51 Shipped" Then
        For counter = 1 To lastColumn
          If (Me.Cells(1, counter).Value = "Sales" Or Me.Cells(1, counter).Value = "Production") _
          And IsEmpty(Me.Cells(Target.Row, counter).Value) Then
            Me.Cells(Target.Row, counter).Value = "Rollup"
          End If
        Next counter
      End If

Then I have this one to return "x" in column AX (50th column) if the last Sales column has "Title Transfer".

Dim r As Range, r1 As Range, counter As Long
Dim MaxCol As Variant, rg As Range, j As Long

  If Not Intersect(Target, Range("N:AP")) Is Nothing And Target.Column Mod 4 = 2 Then
    Set r = Intersect(Target, Cells(1, 1).CurrentRegion, Columns(Target.Column).Resize(, 3))
    Call DoCells(r)
  End If

'Automatically put "x" if Title Transfer in any Sales columns
  If Not Intersect(Target, Range("N:AP")) Is Nothing And Target.Column Mod 4 = 2 Then
    If Target.CountLarge > 1 Then Exit Sub
    Set rg = Range("N" & Target.Row & ":AP" & Target.Row)
    MaxCol = 0
    For j = Columns("AP").Column To Columns("N").Column Step -4
      If Cells(Target.Row, j) <> "" Then
        If j > MaxCol Then MaxCol = j
      End If
    Next
    If MaxCol Mod 4 = 2 Then
      If Cells(Target.Row, MaxCol).Value = "Title Transfer" Then
        Cells(Target.Row, 50).Value = "x"
      Else
        Cells(Target.Row, 50).Value = ""
      End If
    End If
  End If
'This I have 8 Sales Column, however, I only put 1 line down for demonstration
Set r = Intersect(Target, Cells(1, 1).CurrentRegion, Columns(colSales1).Resize(, 3))
    If Not r Is Nothing Then Call DoCells(r)

If Not Intersect(Target, Range("N:AP")) Is Nothing And Target.Column Mod 4 = 2 Then
    If Target.CountLarge > 1 Then Exit Sub
    Set rg = Range("N" & Target.Row & ":AP" & Target.Row)
    MaxCol = Evaluate("=MAX(IF(" & rg.Address & "<>"""",COLUMN(" & rg.Address & ")))")
    If MaxCol Mod 4 = 2 Then
      If Cells(Target.Row, MaxCol).Value = "Title Transfer" Then
        Cells(Target.Row, 50).Value = "x"
      Else
        Cells(Target.Row, 50).Value = ""
      End If
    End If
  End If
End Sub

Can you please advise how I can fix the following codes so that they do not class with each anymore?


FYI, I have 8 Days in total, each Day is a combination of 4 columns with the exact same order: Sales, Production, Day, Status. The range is from column N to column AS.

As seen from the the Master Worksheet tab in my sample file:
1) the 1st row did exactly what I wanted. It did evaluate correctly the "Title Transfer" in column Sales/column R (of Day 2) to be the last Sales column that has "Title Transfer" and return an "x" in column AX

2) the 2nd row, the codes returned both correct and wrong results. I had put "Title Transfer" in a Sales column first, which the Macro then returned an "x" in column AX. That's correct.
However, when I put "Shipped" in column AU after having put "Title Transfer" first in column AX, the "x" was replaced by the Shipped codes I posted above. It did return "Rollup" for all empty Sales and Production cells when I put "Shipped" in column AU (47th column). But the "x" to indicate Title Transfer was gone.
So here comes the problem I've been struggling with for the past week. Can you please advise how I can fix this problem? I suspect that when the empty Sales and Production cells are set to "Rollup" then "Title Transfer" is no longer the last Sales column (MaxCol) and the else condition Cells(Target.Row, 50).Value = "" removes the "x". Still, not sure how to fix that problem.

3) the 3rd row is what I wanted my codes did if both "Shipped" in column AU and "x" in column AX (please also help me with this one)

Can you please help how to get it to work that way? Thanks a lot and please let me know if you need more info.

 

[StephenCrump]

It looks like your code is in Sub Worksheet_Change, and makes changes to cells in that sheet. Each write to a cell will trigger another Worksheet_Change, and other parts of the Sub may execute in ways that you didn't intend.

To stop this happening you need:

Application.EnableEvents = False
'make changes to the worksheet
Application.EnableEvents = True

See if that helps. If you're still having problems, we can take a more detailed look at the code.

 

[alisoncleverly]

It looks like your code is in Sub Worksheet_Change, and makes changes to cells in that sheet. Each write to a cell will trigger another Worksheet_Change, and other parts of the Sub may execute in ways that you didn't intend.

To stop this happening you need:

Application.EnableEvents = False
'make changes to the worksheet
Application.EnableEvents = True

See if that helps. If you're still having problems, we can take a more detailed look at the code.

Hi Stephen,

Thanks for the suggestion, it partially worked since the "x" no longer disappears when I put "Shipped" after. However, another problem comes since putting Application.EnableEvents seems to stop another part of my codes. The below code was written so that it will automatically return the approriate value to Day cells based on Sales and Production values.

Public Sub MasterChange(SPD As Range)
    Dim rSales As Range
    Dim rProduction As Range
    Dim rDay As Range

    Set rSales = SPD.Cells(1, 1)
    Set rProduction = SPD.Cells(1, 2)
    Set rDay = SPD.Cells(1, 3)

    Application.EnableEvents = False
    If rSales = "Rollup" And rProduction = "Rollup" Then
        rDay = "Rollup"
    ElseIf rSales = "Rollup" And rProduction = "Green" Then
        rDay = "Green"
    ElseIf rSales = "Rollup" And rProduction = "Yellow" Then
        rDay = "Yellow"
'I have approximately 40 Ifs statements like those but above are just a few for demonstration
    End If
        Application.EnableEvents = True
    End Sub

After nesting the Application.EnableEvents, the macro no longer evaluates and returns values to Day cells, although the "x" in column AX no longer disappears when "Shipped" is put in column AU.

Can you please advise? Thanks a lot!

 

[StephenCrump]

I'd like to avoid diving into the detail of the code unless necessary, as it will be hard to replicate your layout.

You have Worksheet_Change code that potentially does different things to different cells, depending on which cells are changed. So you may need to turn .EnableEvents off/on several times, depending on how you'd like the code to respond. So your code might look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    'Code that monitors changes in section A of the sheet
    Application.EnableEvents = False
    'make other changes to section A, but don't trigger another Worksheet_Change
    Application.EnableEvents = True
    
    'make changes to section B, and DO trigger Worksheet_Change so that section B code can run
    '------------------------------------------------------------------------------------------
    
    'Code that monitors changes in section B of the sheet
    'Do stuff
    '------------------------------------------------------------------------------------------
    
    'Code that monitors changes in section C of the sheet
    Application.EnableEvents = False
    'make changes to section D
    'If Worksheet_Change is triggered, all sections of the code A, B and C will be skipped
    ' so the extra call will have no effect, but it's better not to trigger in the first place
    Application.EnableEvents = True
    '------------------------------------------------------------------------------------------

End Sub

Another thing to think about is whether all this code needs to be in Worksheet_Change? In my example above, changes to section A of the sheet will cause the code to change section B which will generate a call to Worksheet_Change which will run Section B of the code.

Do you want user changes to section B of the Sheet to also run section B of the code? If not, we can make Section B a separate Sub and call it from section A, or incorporate the code into section A.

 

[StephenCrump]

I might add, there are other things you can do with code, e.g. call Worksheet_Change() direct, or have Worksheet_Change() respond differently depending on whether the change is user-generated or code-generated.

The key point is to be clear about the logic - which bits of code do you want to be triggered (or not triggered) and in what circumstances.