Conflict Of Macros In Worksheet

[MathewPBennett]

Good Evening All,

I seem to have a conflict of macros in a single worksheet.
I have a simple event change that inserts todays date when 't' is entered:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("AK:AK"), Target) Is Nothing Then
If Target = "t" Then
Target.Value = Format(Now, "mm-dd-yy hh:mm:ss")

End If
End If
End Sub


This seems to work fine, however I have another pice of code, which is attached to
a button and inserts rows:

Sub CommandButton1_Click()

' InsertRowsAboveTotals()
' InsertRowsAndFillFormulas(Optional vRows As Long)
'Dim vRows As Integer
'Dim irow As Long, i As Long
' row selection based on "Totals" cell
Columns("G:G").Find(What:="total", After:=Range("G2"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Offset(-2, 0).Activate
ActiveCell.EntireRow.Select
If vRows <> 1 Then
vRows = Application.InputBox(prompt:= _
"Enter Number Of Rows To Insert." & vbNewLine & _
"Or 'OK' For Default 10 Rows." & vbNewLine & _
"Or 'Cancel'.", _
Title:="Add Rows", Default:="")
End If
If vRows = False Then Exit Sub
If vRows = "" Then
vRows = 10

End If
ActiveSheet.Select
Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next
'to handle no constants in range to remove the non-formulas
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
irow = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Activate
End Sub



Now, when I try to use the button to insert rows, I get a type mismatch error
on the line of code (change_event):

If Target = "t" Then


This is diving me bonkers.

Any help here from VBA experts would be very very welcome

Yours, with no hair left,

Mathew

 

[Jim Rech]

Without having tried to understand the CommandButton1_Click macro completely
I would suggest that you add:

Application.EnableEvents = False

to it near the beginning and reverse this at the end. With events disabled
the Worksheet_Change code will not be called when you click the button.

 

[MathewPBennett]

Dear Jim,
Thank you so much.
Thanks also to Harald for similar response, and detail of defining the Target -
I got your response via developers dex.
Many Best Wishes,
Mathew

Without having tried to understand the CommandButton1_Click macro completely
I would suggest that you add:

Application.EnableEvents = False

to it near the beginning and reverse this at the end. With events disabled
the Worksheet_Change code will not be called when you click the button.

 

[Harald Staff]

You're welcome Mathew. Hope it's of help.
Every added piece of knowledge raises at least two new questions, so the more we learn the
less we know. ;-)
Good luck, and happy holidays.

Best wishes Harald
Followup to newsgroup only please.