Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto insert/delete on other sheet
I have two sheets in my workbook, sheet1 and sheet2.
Is it possible to create some sort of function or macro that can automatically delete/insert the same row in sheet2 if I delete/insert row(s) in sheet1 ? For example, if I Insert Row 15 in sheet1 and it also will insert Row 15 in sheet2, Delete Row 20 in sheet1 and it will delete Row 20 in sheet2. I try to use event change, but don't know how to combine it with onkey {insert/delete). Please help. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto insert/delete on other sheet
Click on the sheet tab for sheet2. Hold down the Ctrl key and click on the
Sheet Tab for Sheet1. Now make you insertions and deletions. Now right click on one of the sheet tabs and select ungroup. Changes made in group mode happen to all grouped sheets. -- Regards, Tom Ogilvy "broogle" wrote in message oups.com... I have two sheets in my workbook, sheet1 and sheet2. Is it possible to create some sort of function or macro that can automatically delete/insert the same row in sheet2 if I delete/insert row(s) in sheet1 ? For example, if I Insert Row 15 in sheet1 and it also will insert Row 15 in sheet2, Delete Row 20 in sheet1 and it will delete Row 20 in sheet2. I try to use event change, but don't know how to combine it with onkey {insert/delete). Please help. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto insert/delete on other sheet
Hi Tom !
It works !! Thank you. Is it possible to lock this grouping, just in case someone will accidently select ungroup? Thanks again Tom. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto insert/delete on other sheet
Hi Tom.
Sorry this is not what I really want. What I mean was the automatic update is only for delete/insert only, not for everything. Because some data in sheet2 still need independent. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto insert/delete on other sheet
I don't think you can lock it, you might be able to write code in the
activate event of sheet1 to reestabish it. Just be aware that almost any changes you make to sheet1 will happen to sheet 2 also. Right click on the sheet tab of Sheet1 and select view code. Put in this code: Private Sub Worksheet_Activate() Application.EnableEvents = False Worksheets(Array("Sheet1", "Sheet2")).Select Application.EnableEvents = True End Sub In the Thisworkbook module (in the vbe (Alt+F11), for you workbook, double click on the ThisWorkbook entry and in the module put in this code) Private Sub Workbook_Open() Application.EnableEvents = False Worksheets(Array("Sheet1", "Sheet2")).Select Application.EnableEvents = True End Sub You could repeat the first code for Sheet2 if you never want to be able to go to sheet2 but always had it grouped Then you could run code like this to get to it yourself Sub GotoSheet2() Application.EnableEvents = False worksheets("Sheet2").Activate Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "broogle" wrote in message ups.com... Hi Tom ! It works !! Thank you. Is it possible to lock this grouping, just in case someone will accidently select ungroup? Thanks again Tom. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto insert/delete on other sheet
So forget my code based on your follow up post.
There is not specific event associated with inserting and deleting rows. In later versions of excel, inserting or deleting a row triggers the change event, but so does a lot of other things. The target would be the row address, but it would be the same if you just cleared a row and it doesn't distinguish between inserting and deleting. Nonetheless, since you know the layout of your sheet, maybe you can use that information to differentiate and react to a row deletion or insertion. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... I don't think you can lock it, you might be able to write code in the activate event of sheet1 to reestabish it. Just be aware that almost any changes you make to sheet1 will happen to sheet 2 also. Right click on the sheet tab of Sheet1 and select view code. Put in this code: Private Sub Worksheet_Activate() Application.EnableEvents = False Worksheets(Array("Sheet1", "Sheet2")).Select Application.EnableEvents = True End Sub In the Thisworkbook module (in the vbe (Alt+F11), for you workbook, double click on the ThisWorkbook entry and in the module put in this code) Private Sub Workbook_Open() Application.EnableEvents = False Worksheets(Array("Sheet1", "Sheet2")).Select Application.EnableEvents = True End Sub You could repeat the first code for Sheet2 if you never want to be able to go to sheet2 but always had it grouped Then you could run code like this to get to it yourself Sub GotoSheet2() Application.EnableEvents = False worksheets("Sheet2").Activate Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "broogle" wrote in message ups.com... Hi Tom ! It works !! Thank you. Is it possible to lock this grouping, just in case someone will accidently select ungroup? Thanks again Tom. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto insert/delete on other sheet
Hi Tom,
Is it possible to limit the action only for delete/insert ? I don't want all the changes in sheet1 being updated to sheet2. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto insert/delete on other sheet
No, not using grouping.
-- Regards, Tom Ogilvy "broogle" wrote in message oups.com... Hi Tom, Is it possible to limit the action only for delete/insert ? I don't want all the changes in sheet1 being updated to sheet2. Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto insert/delete on other sheet
Do you mean delete/insert in Excel 2000 will not trigger the change
event? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto insert/delete on other sheet
Frankly, I can't remember whether it does or not and I don't have a copy
handy to test with. I know it doesn't do it in xl97. I tested it in xl2002, so I know it does there. Easiest is to test it in a new blank workbook, put this into sheet1. Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Address End Sub Then delete a row. Then insert a row. If you do get a message, it does, conversely if you don't get a message, it doesn't. <g -- Regards, Tom Ogilvy "broogle" wrote in message oups.com... Do you mean delete/insert in Excel 2000 will not trigger the change event? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto insert/delete on other sheet
It does.
Thanks a lot Tom, I will try to work it out from here. God bless. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert nd delete row in protect sheet | Excel Discussion (Misc queries) | |||
Capturing Insert/Delete Events in Excel Sheet using c# | Excel Worksheet Functions | |||
Macro auto insert/delete | Excel Discussion (Misc queries) | |||
how to insert row or delete while the auto filter is turn on? | Excel Discussion (Misc queries) | |||
Auto-Insert & Delete Comments | Excel Programming |