Conflict
 

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

Conflict
 

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


--
HTH. Best wishes Harald
Followup to newsgroup only please.

"MathewPBennett" wrote in message
...
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

Conflict
 

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

Conflict
 

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