Conflict

[MathewPBennett]

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

 

[Harald Staff]

Hi Mathew

If Target = "t" Then

Target what equals "t" ? Color ? Fonstize ? Name ? Address ? Not specified means default
means Value, but you should specify this, because Target is a range; a range is one or
more cells. And I think that's what happens, you fill, and suddenly Target is a bunch of
cells, and a bunch iof cells doesn't have a Value property and certainly doesn't equal
"t".

Among workarounds try one or more of these:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 then Exit Sub
If Target(1).Value = "t" Then
' stuff while you wait ..........

Sub CommandButton1_Click()
Application.EnableEvents = False
' all code
Application.EnableEvents = True
End Sub

 

[J.E. McGimpsey]

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

 

[MathewPBennett]

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
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