error when running cut & paste macro

[Redskinsfan]

I had the following macro workin fine prior to adding a macro to the sheet.
here's the macro im having problems with i get the error message on
ActiveSheet.paste..

below this macro i will add the new macro added to sheet which started
causing for the macro above to stop working.

Sub New_Trade()
'
' New_Trade Macro
' Macro recorded 8/2/2006 by Parsons User
'
ActiveSheet.Protect UserInterfaceOnly:=True
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-2, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A11").Select
Application.CutCopyMode = False
End Sub

Below this is the macro i added to the whole sheet which now is causing my
other macros to give me the error on the paste line to stop..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Protect UserInterfaceOnly:=True
Cells.FormatConditions.Delete
With Target
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
End With
.FormatConditions(1).Interior.ColorIndex = 19
End With

End Sub

Im a newbie with macros and most of these i have taken from samples so
please when explaining would apreciate if done in simple terms.. Thanks in
advance

 

[Otto Moehrbach]

The problem is the placement of the 2 macros. The first one belongs in a
regular module. If it was working before, then you have it in the right
place. Leave it there.
The second macro is what is called a "sheet macro" or a "sheet event
macro". It has to go into a different type of module, called a "sheet
module". Every sheet has a sheet module of its own. This type of macro
fires automatically upon the occurrence of some event in that sheet. In
this case, the event is the selection of any cell in the entire sheet. Is
that what you wanted with this macro? You don't have any code in that macro
to narrow down the range in which a selection will result in some action
being taken. Right now you will get the action with any cell being
selected. Any cell in the entire sheet! The action is adding borders and
colors. Exactly what did you want this second macro to do? HTH Otto
PS: To access the sheet module of a sheet, right-click on the sheet tab,
select View Code. There is the sheet module for that sheet.

 

[Redskinsfan]

Hi otto,

ok well the 1st macro i have under the worksheet with all the others, this
is a sheet that was created to serve as a form so i have locked all other
cells, however they still need to add new rows at times so this allows them
to cut and paste a new row.

2nd macro is under the sheet module, this was to highlite the cell they're
in just a more visual help macro also some cells have shading so you will see
that it suppose to revert to original formating.

the 2nd macro works fine highlites as it should.
the 1st macro once you add the second it will ask to debug when it gets to

ActiveSheet.paste, however if i remove the 2nd macro from the sheet module i

dont get that error just works fine. I take it is something within the second
macro but im not sure what as these macros were just taken from samples.
Thanks in advance

 

[Otto Moehrbach]

Now I understand what you are doing. The problem is actually with the first
macro. Remember that the second macro fires whenever a cell selection is
made in that sheet, whether it's one cell or multiple cells being selected.
Like selecting a row. Well the first macro selects cells here and there and
this is causing the second macro to fire and you don't have the necessary
code in the second macro to trap the error. Try this. Go to the first
macro and add this line at the beginning of the macro right after the Sub
New_Trade() line:
Application.EnableEvents = False
Then add this next line as the last line just before the End Sub line:
Application.EnableEvents = True
These lines tell Excel to ignore the events created while the first macro is
running. This prevents the second macro from firing during the running of
the first macro. HTH Otto

 

[Redskinsfan]

Otto,
Man thanks alot that works great..originally i had even pasted the
New_Trade( ) line and i was gettin an error but i got rid of it and worked
like a charm..

say could i perhaps bother you with some other questions in regards to some
macros and cuting and pasting procedure. if not is ok i really appreciate the
help with my original question.

 

[Otto Moehrbach]

Sure. My email address is (e-mail address removed). Remove the "nop" from
this address. Otto