Thank you very much gentlemen,
your solutions have saved me much trial & error.
I realy should get JW's book! Maybe I'll treat myself from amazon this christmas!
Cheers again guys.
Mathew
"J.E. McGimpsey" wrote in message ...
First, turn off events when you're inserting your rows:

Application.EnableEvents = False
'your code here
Application.EnableEvents = True

Second, your Format(..) in your worksheet_Change() macro isn't doing
anything - XL will parse the entry and display the value in whatever
format the cell is currently set to. Try:

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




In article ,
"MathewPBennett" wrote:

Good Evening All,
I have already posted this to misc. but it has not appeared for ages, so am
re-posting here. I hope that is ok.

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



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003