Sometimes when i get type mismatch error while running macro excel shows
error messege box and then opens vb code section and then i have to click
[end]. After that macro won't trigger anymore. Is there any way to reset
macro trigerring without closing & reopening excel ? Cuz this is what i
ussualy do if i encounter this error.

Do you mean event code? If so just type Application.EnableEvents = True in
the immediate window.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
Sometimes when i get type mismatch error while running macro excel shows
error messege box and then opens vb code section and then i have to click
[end]. After that macro won't trigger anymore. Is there any way to reset
macro trigerring without closing & reopening excel ? Cuz this is what i
ussualy do if i encounter this error.

This is the code that creates type mismatch error.
Fow examle if i try to delete entire row or copy entire row... and so on

As you can see i already have "Application.EnableEvents = True"

Sub Worksheet_Change(ByVal Target As Range)
25
If Target.Column = 1 And Target.Value = "Priv" Then
'Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).Value = "-"
Cells(Target.Row, 2).Value = "-"
Cells(Target.Row, 3).Select
Else
'Whatever original value or formulae for B and C
End If
Application.EnableEvents = True
End Sub

"Bob Phillips" wrote:

Do you mean event code? If so just type Application.EnableEvents = True in
the immediate window.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
Sometimes when i get type mismatch error while running macro excel shows
error messege box and then opens vb code section and then i have to click
[end]. After that macro won't trigger anymore. Is there any way to reset
macro trigerring without closing & reopening excel ? Cuz this is what i
ussualy do if i encounter this error.

Mikus. I warned you earlier to stay away from this event with the worksheet
you were developing. But it seems you are plunging head-first into it
anyway. What is happening? If the activecell is in column A and the value =
"Priv", Then the code that follows is going to run. If you delete the entire
row then the cells are gone that the code was looking to do something with.
Bingo, Runtime Error. Similarly with a copy. The copy is giving those cells
one value while the code is trying to give it another. Mixed messages to the
same cells gets a Runtime Error.
The workaround is to further limit the code from running when you don't want
it to. You have so far limited it to the entire column of A (do you really
need all 65,536 rows?) plus only if that cell = "Priv". Now you need to
limit the number of cells that are in Target before the code should run.
When you select an entire row , then Target As Range has 256 cells in it.
You need to tell the Event NOT to run if there is more than 1 cell in
Target.

Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
'Your code
End Sub

This will kill the code whenever more than 1 cell is selected.
If you want to further restrict the code to only some cells in column A,
then say so, and we can deal with that.

Mike F

"Mikus" wrote in message
...
This is the code that creates type mismatch error.
Fow examle if i try to delete entire row or copy entire row... and so on

As you can see i already have "Application.EnableEvents = True"

Sub Worksheet_Change(ByVal Target As Range)
25
If Target.Column = 1 And Target.Value = "Priv" Then
'Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).Value = "-"
Cells(Target.Row, 2).Value = "-"
Cells(Target.Row, 3).Select
Else
'Whatever original value or formulae for B and C
End If
Application.EnableEvents = True
End Sub

"Bob Phillips" wrote:

Do you mean event code? If so just type Application.EnableEvents = True
in
the immediate window.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
Sometimes when i get type mismatch error while running macro excel
shows
error messege box and then opens vb code section and then i have to
click
[end]. After that macro won't trigger anymore. Is there any way to
reset
macro trigerring without closing & reopening excel ? Cuz this is what i
ussualy do if i encounter this error.

Yes but you said it happened after an error in the macro, so I was
suggesting doing it in teh VBE to reset events.

Seeing the code though I don't know where the events get turned off. And
where did Private go in the event declaration?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
This is the code that creates type mismatch error.
Fow examle if i try to delete entire row or copy entire row... and so on

As you can see i already have "Application.EnableEvents = True"

Sub Worksheet_Change(ByVal Target As Range)
25
If Target.Column = 1 And Target.Value = "Priv" Then
'Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).Value = "-"
Cells(Target.Row, 2).Value = "-"
Cells(Target.Row, 3).Select
Else
'Whatever original value or formulae for B and C
End If
Application.EnableEvents = True
End Sub

"Bob Phillips" wrote:

Do you mean event code? If so just type Application.EnableEvents = True
in
the immediate window.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
Sometimes when i get type mismatch error while running macro excel
shows
error messege box and then opens vb code section and then i have to
click
[end]. After that macro won't trigger anymore. Is there any way to
reset
macro trigerring without closing & reopening excel ? Cuz this is what
i
ussualy do if i encounter this error.

Thank you Mike!

"Mike Fogleman" wrote:

Mikus. I warned you earlier to stay away from this event with the worksheet
you were developing. But it seems you are plunging head-first into it
anyway. What is happening? If the activecell is in column A and the value =
"Priv", Then the code that follows is going to run. If you delete the entire
row then the cells are gone that the code was looking to do something with.
Bingo, Runtime Error. Similarly with a copy. The copy is giving those cells
one value while the code is trying to give it another. Mixed messages to the
same cells gets a Runtime Error.
The workaround is to further limit the code from running when you don't want
it to. You have so far limited it to the entire column of A (do you really
need all 65,536 rows?) plus only if that cell = "Priv". Now you need to
limit the number of cells that are in Target before the code should run.
When you select an entire row , then Target As Range has 256 cells in it.
You need to tell the Event NOT to run if there is more than 1 cell in
Target.

Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
'Your code
End Sub

This will kill the code whenever more than 1 cell is selected.
If you want to further restrict the code to only some cells in column A,
then say so, and we can deal with that.

Mike F

"Mikus" wrote in message
...
This is the code that creates type mismatch error.
Fow examle if i try to delete entire row or copy entire row... and so on

As you can see i already have "Application.EnableEvents = True"

Sub Worksheet_Change(ByVal Target As Range)
25
If Target.Column = 1 And Target.Value = "Priv" Then
'Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).Value = "-"
Cells(Target.Row, 2).Value = "-"
Cells(Target.Row, 3).Select
Else
'Whatever original value or formulae for B and C
End If
Application.EnableEvents = True
End Sub

"Bob Phillips" wrote:

Do you mean event code? If so just type Application.EnableEvents = True
in
the immediate window.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
Sometimes when i get type mismatch error while running macro excel
shows
error messege box and then opens vb code section and then i have to
click
[end]. After that macro won't trigger anymore. Is there any way to
reset
macro trigerring without closing & reopening excel ? Cuz this is what i
ussualy do if i encounter this error.