Macro Interference?

Question

Excel 2010 ... I Record Macros only ... Sometimes followed by a creative cut/paste ... & I occassionally get small pieces of Code from those intimate with VBA ...

Ok ... I have 2 Macros in my WB which worked perfect in seperate WBs, but appear to conflict when I setup in same WB when both are run ...

WS Macro (below) ... Automatically, chgs value of Cell entry by factor displayed in Row 10.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rC As Range

If Intersect(Target, Range("14:37")) Is Nothing Then Exit Sub

On Error Resume Next

Application.EnableEvents = False

For Each rC In Intersect(Target, Range("14:37"))

If Cells(10, rC.Column).Value <> "" And rC.Value <> "" Then

rC.Value = rC.Value * Cells(10, rC.Column).Value

End If

Next rC

Application.EnableEvents = True

End Sub

Module Macro (Instruction below embedded in Recorded Code) reviews WS for Lower Case Letters & if it finds them sets them to Upper Case ... Issue is ... the below Code causes the above Code to activate & thus resets the Values a 2nd time ... So if 2 became 4 by Macro above ... the 4 would now becomes 8 by the Macro Inst below ... This not desired ... Once Macro above sets the Cell Value it needs to remain "when/if"  Lower Case Ltrs need to be made Upper Case.

Below proceded by YES/NO Msg (Are all Codes Caps?) ... YES (skips) ... NO (Runs) the following Instruction which is now resetting the Cell Values a 2nd time.

    For Each x In Range("G14:CB37")

      x.Formula = UCase(x.Formula)

      Next

    Else

   End If

Thats my story ... So once again I am turning to those that are intimate with VBA & who support these boards for a solution ...

Thanks ... Kha

Answer 1

The Worksheet_Change worksheet macro is triggered by an event, specifically the change in one or more of the values on the worksheet. To avoid this, disable events in the module macro like this,

Application.EnableEvents = False

    For Each x In Range("G14:CB37")

      x.Formula = UCase(x.Formula)

      Next

  Application.EnableEvents = True

With this in place, changing the case of the value will not trigger an event. I've guessed where to put the two statements. The best place is as the first statement within a sub macro and just prior to leaving it.

Answer 2

In the module macro, insert

Application.EnableEvents = False

above the loop that sets the cells to upper case, and

Application.EnableEvents = True

below it.

Answer 3

Perfect ... :)

Thanks for supporting these boards ... K