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
Yours, with no hair left,
Mathew
---
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