Conflict of two macros?

[Shivam.Shah]

Hi all,

I have the below two codes in my worksheet, the first one is run using
a button and the second one is an automatic macro to enter fixed
dates.

There seems to be a conflict, as each time I use the button to add a
row, I get an error, and when I go to debug it highlights this row in
the second macro --> If Target.Value = "" Then

Is there a solution? I can't see why there is an error, as both are
separate codes?

Any help will be appreciated! Thanks!

*********************
***The first code:

Private Sub CommandButton1_Click()
For i = 1 To 10000
If ActiveSheet.Range("C" & i).Value = "BLENDING FACILITIES"
Then
Range("C" & (i + 1) & ":" & "AN" & (i + 1)).Select
Selection.Copy
Selection.Insert Shift:=xlDown

For k = 1 To 40
If ActiveSheet.Cells(i + 1, k).HasFormula = False Then
ActiveSheet.Cells(i + 1, k).ClearContents
End If
Next
Exit For
End If
Next
End Sub

***The second code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const ColumnsToCheck As String =
"C:C,F:F,I:I,L:L,O:O,R:R,U:U,X:X,AA:AA,AD:AD,AG:AG,AJ:AJ"
If Not Intersect(Target, Range(ColumnsToCheck)) Is Nothing Then
If Target.Value = "" Then
Target.Offset(0, 1).Value = "-"
Else
Target.Offset(0, 1).Value = Now()
End If
End If
End Sub

 

[Ron Rosenfeld]

Hi all,

I have the below two codes in my worksheet, the first one is run using
a button and the second one is an automatic macro to enter fixed
dates.

There seems to be a conflict, as each time I use the button to add a
row, I get an error, and when I go to debug it highlights this row in
the second macro --> If Target.Value = "" Then

Is there a solution? I can't see why there is an error, as both are
separate codes?

Any help will be appreciated! Thanks!

*********************
***The first code:

Private Sub CommandButton1_Click()
For i = 1 To 10000
If ActiveSheet.Range("C" & i).Value = "BLENDING FACILITIES"
Then
Range("C" & (i + 1) & ":" & "AN" & (i + 1)).Select
Selection.Copy
Selection.Insert Shift:=xlDown

For k = 1 To 40
If ActiveSheet.Cells(i + 1, k).HasFormula = False Then
ActiveSheet.Cells(i + 1, k).ClearContents
End If
Next
Exit For
End If
Next
End Sub

***The second code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const ColumnsToCheck As String =
"C:C,F:F,I:I,L:L,O:O,R:R,U:U,X:X,AA:AA,AD:AD,AG:AG,AJ:AJ"
If Not Intersect(Target, Range(ColumnsToCheck)) Is Nothing Then
If Target.Value = "" Then
Target.Offset(0, 1).Value = "-"
Else
Target.Offset(0, 1).Value = Now()
End If
End If
End Sub

Without looking too deeply into your code, I believe your event code will fail
if Target is a multi-cell range. You probably need to check for that, and, if
it's the problem, rewrite your event code to take that into account.
--ron

 

[Dave Peterson]

The first code makes changes when it copies and pastes. Should those changes be
processed by the worksheet_change event?

If no, then add this to the _click event:

Application.enableevents = false
'code to do the copy|paste and clear contents
application.enableevents = true

If the _click event's changes should be processed by the _change event, then
you'll have to look at each of the cells in the range that's changing.

dim myIntersect as range
dim myCell as range

set myintersect = intersect(target, me.range(ColumnsToCheck))

if myintersect is nothing then
exit sub
end if

for each mycell in myintersect.cells
If mycell.Value = "" Then
mycell.Offset(0, 1).Value = "-"
Else
mycell.Offset(0, 1).Value = Now()
End If
next mycell

 

[Dave Peterson]

ps. These changes will cause the _change event to call itself:

If Target.Value = "" Then
Target.Offset(0, 1).Value = "-"
Else
Target.Offset(0, 1).Value = Now
End If
End if

I'd disable events first:

application.enableevents = false
If Target.Value = "" Then
Target.Offset(0, 1).Value = "-"
Else
Target.Offset(0, 1).Value = Now
End If
end if
application.enableevents = true

=======
Same thing with my previous suggestion:

application.enableevents = false
for each mycell in myintersect.cells
If mycell.Value = "" Then
mycell.Offset(0, 1).Value = "-"
Else
mycell.Offset(0, 1).Value = Now
End If
next mycell
application.enableevents = true

 

[Shivam.Shah]

Thanks Dave! It works perfectly now and no errors are triggered in the
sheet!

Thanks again!

 

[Shivam.Shah]

Thanks ron! I understood the problem after looking at yours and Dave's
posts!