Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
error when running cut & paste macro
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" wrote in message ... 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("A1:D1").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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
error when running cut & paste macro
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" wrote: 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" wrote in message ... 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("A1:D1").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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
error when running cut & paste macro
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" wrote in message ... 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" wrote: 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" wrote in message ... 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("A1:D1").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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
error when running cut & paste macro
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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("A1:D1").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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
error when running cut & paste macro
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro - select and paste | Excel Worksheet Functions | |||
Macro to run on the "Active Sheet" | Excel Discussion (Misc queries) | |||
Need a macro to Copy a selection and paste into a new email. | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Copy Paste macro | Excel Discussion (Misc queries) |