Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with sheets that mirror each other
I'm trying to setup that mirror each other at all times. I am currently
handling "Worksheet_Change()" in my sheets, so any time one sheet gets modified, I am trying to copy the contents of one sheet to the other. The shortened code looks as follows: ' ' Sheet 1 ' Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value End Sub ' ' Sheet 2 ' 'Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet1.Range(changedCell).Value = Sheet2.Range(changedCell).Value End Sub This code generally works when a user changes one cell at a time. However, (for example) if a user selects a big block of cells on Sheet1 and presses the DELETE key, that block of cells will actually delete on Sheet1, but only one (or two or so) cells will delete from Sheet2. How can I modify the subroutines above to handle cases when a block of data is highlighted in one sheet, then tell the second sheet to delete all those cells as well?? Thank you!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with sheets that mirror each other
Hi,
Sheet1 Private Sub Worksheet_Change(ByVal Target As Range) Sheets("Sheet2").Range(Target.Address).Value = Target.Value End Sub sheet2 Private Sub Worksheet_Change(ByVal Target As Range) Sheets("Sheet1").Range(Target.Address).Value = Target.Value End Sub Mike "Robert Crandal" wrote: I'm trying to setup that mirror each other at all times. I am currently handling "Worksheet_Change()" in my sheets, so any time one sheet gets modified, I am trying to copy the contents of one sheet to the other. The shortened code looks as follows: ' ' Sheet 1 ' Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value End Sub ' ' Sheet 2 ' 'Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet1.Range(changedCell).Value = Sheet2.Range(changedCell).Value End Sub This code generally works when a user changes one cell at a time. However, (for example) if a user selects a big block of cells on Sheet1 and presses the DELETE key, that block of cells will actually delete on Sheet1, but only one (or two or so) cells will delete from Sheet2. How can I modify the subroutines above to handle cases when a block of data is highlighted in one sheet, then tell the second sheet to delete all those cells as well?? Thank you!!! . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with sheets that mirror each other
Hi Robert,
Perhaps you would like to share the reason for mirroring the 2 sheets. There might be a more efficient way of achieving this like copying the sheet before close or save etc. -- Regards, OssieMac "Robert Crandal" wrote: I'm trying to setup that mirror each other at all times. I am currently handling "Worksheet_Change()" in my sheets, so any time one sheet gets modified, I am trying to copy the contents of one sheet to the other. The shortened code looks as follows: ' ' Sheet 1 ' Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value End Sub ' ' Sheet 2 ' 'Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet1.Range(changedCell).Value = Sheet2.Range(changedCell).Value End Sub This code generally works when a user changes one cell at a time. However, (for example) if a user selects a big block of cells on Sheet1 and presses the DELETE key, that block of cells will actually delete on Sheet1, but only one (or two or so) cells will delete from Sheet2. How can I modify the subroutines above to handle cases when a block of data is highlighted in one sheet, then tell the second sheet to delete all those cells as well?? Thank you!!! . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with sheets that mirror each other
I have TWO different layouts, a red and blue layout, each with
slightly different dimensions. It gives the user 2 different ways of instantly looking at the data. When the red sheet is toggled on, I hide the blue sheet and the unhide the red sheet (or vice versa). So, you see, the user only sees one sheet at a time, and I want them to have similar data at all times. My mirror plan works except for case when a block is highlighted and then deleted with the "Delete" key, as the deleted cells dont all seem to copy over for some reason. Got any ideas? "OssieMac" wrote in message ... Hi Robert, Perhaps you would like to share the reason for mirroring the 2 sheets. There might be a more efficient way of achieving this like copying the sheet before close or save etc. -- Regards, OssieMac "Robert Crandal" wrote: I'm trying to setup that mirror each other at all times. I am currently handling "Worksheet_Change()" in my sheets, so any time one sheet gets modified, I am trying to copy the contents of one sheet to the other. The shortened code looks as follows: ' ' Sheet 1 ' Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value End Sub ' ' Sheet 2 ' 'Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet1.Range(changedCell).Value = Sheet2.Range(changedCell).Value End Sub This code generally works when a user changes one cell at a time. However, (for example) if a user selects a big block of cells on Sheet1 and presses the DELETE key, that block of cells will actually delete on Sheet1, but only one (or two or so) cells will delete from Sheet2. How can I modify the subroutines above to handle cases when a block of data is highlighted in one sheet, then tell the second sheet to delete all those cells as well?? Thank you!!! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with sheets that mirror each other
Isn't my code below similar to yours??? I'm just using a
different notation, am I not?? I don't think either code will work for cases when a big area of cells is highlighted and deleted (with "delete" key), as the deleted cells dont seem to copy over from one sheet to the other for some reason. "Mike H" wrote in message ... Hi, Sheet1 Private Sub Worksheet_Change(ByVal Target As Range) Sheets("Sheet2").Range(Target.Address).Value = Target.Value End Sub sheet2 Private Sub Worksheet_Change(ByVal Target As Range) Sheets("Sheet1").Range(Target.Address).Value = Target.Value End Sub Mike "Robert Crandal" wrote: I'm trying to setup that mirror each other at all times. I am currently handling "Worksheet_Change()" in my sheets, so any time one sheet gets modified, I am trying to copy the contents of one sheet to the other. The shortened code looks as follows: ' ' Sheet 1 ' Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value End Sub ' ' Sheet 2 ' 'Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet1.Range(changedCell).Value = Sheet2.Range(changedCell).Value End Sub This code generally works when a user changes one cell at a time. However, (for example) if a user selects a big block of cells on Sheet1 and presses the DELETE key, that block of cells will actually delete on Sheet1, but only one (or two or so) cells will delete from Sheet2. How can I modify the subroutines above to handle cases when a block of data is highlighted in one sheet, then tell the second sheet to delete all those cells as well?? Thank you!!! . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with sheets that mirror each other
When using a worksheet change if somebody copies a group of cells you have to run the code for every values in the group like this Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False for each cell in Tartget changedCell = cell.Address Sheet2.Range(changedCell).Value = cell.Value next Cell Application.EnableEvents = True End Sub I'm not sure if this code will work because worksheet change is inteneded only to modify the cells on the active sheet and doesn't alows other cells to get changed. The Delkte key may not work because the worksheet change event occurs after the cell is changed and if somebody removed data from the cell you can't copy the data to another sheet. You could use the function Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Which will occur before the user changes the cell data. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161923 Microsoft Office Help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with sheets that mirror each other
Robert,
No our code is 'similar' and not the same so I suggest you enter my code, select a large range of cells and hit the delete key and see what happens! Mike "Robert Crandal" wrote: Isn't my code below similar to yours??? I'm just using a different notation, am I not?? I don't think either code will work for cases when a big area of cells is highlighted and deleted (with "delete" key), as the deleted cells dont seem to copy over from one sheet to the other for some reason. "Mike H" wrote in message ... Hi, Sheet1 Private Sub Worksheet_Change(ByVal Target As Range) Sheets("Sheet2").Range(Target.Address).Value = Target.Value End Sub sheet2 Private Sub Worksheet_Change(ByVal Target As Range) Sheets("Sheet1").Range(Target.Address).Value = Target.Value End Sub Mike "Robert Crandal" wrote: I'm trying to setup that mirror each other at all times. I am currently handling "Worksheet_Change()" in my sheets, so any time one sheet gets modified, I am trying to copy the contents of one sheet to the other. The shortened code looks as follows: ' ' Sheet 1 ' Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value End Sub ' ' Sheet 2 ' 'Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet1.Range(changedCell).Value = Sheet2.Range(changedCell).Value End Sub This code generally works when a user changes one cell at a time. However, (for example) if a user selects a big block of cells on Sheet1 and presses the DELETE key, that block of cells will actually delete on Sheet1, but only one (or two or so) cells will delete from Sheet2. How can I modify the subroutines above to handle cases when a block of data is highlighted in one sheet, then tell the second sheet to delete all those cells as well?? Thank you!!! . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with sheets that mirror each other
it would be better to disable events
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Sheets("Sheet2").Range(Target.Address).Value = Target.Value Application.EnableEvents = True End Sub same on sheet 2 Mike "Mike H" wrote: Robert, No our code is 'similar' and not the same so I suggest you enter my code, select a large range of cells and hit the delete key and see what happens! Mike "Robert Crandal" wrote: Isn't my code below similar to yours??? I'm just using a different notation, am I not?? I don't think either code will work for cases when a big area of cells is highlighted and deleted (with "delete" key), as the deleted cells dont seem to copy over from one sheet to the other for some reason. "Mike H" wrote in message ... Hi, Sheet1 Private Sub Worksheet_Change(ByVal Target As Range) Sheets("Sheet2").Range(Target.Address).Value = Target.Value End Sub sheet2 Private Sub Worksheet_Change(ByVal Target As Range) Sheets("Sheet1").Range(Target.Address).Value = Target.Value End Sub Mike "Robert Crandal" wrote: I'm trying to setup that mirror each other at all times. I am currently handling "Worksheet_Change()" in my sheets, so any time one sheet gets modified, I am trying to copy the contents of one sheet to the other. The shortened code looks as follows: ' ' Sheet 1 ' Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value End Sub ' ' Sheet 2 ' 'Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet1.Range(changedCell).Value = Sheet2.Range(changedCell).Value End Sub This code generally works when a user changes one cell at a time. However, (for example) if a user selects a big block of cells on Sheet1 and presses the DELETE key, that block of cells will actually delete on Sheet1, but only one (or two or so) cells will delete from Sheet2. How can I modify the subroutines above to handle cases when a block of data is highlighted in one sheet, then tell the second sheet to delete all those cells as well?? Thank you!!! . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with sheets that mirror each other
Joel,
I don't believe you do need to do that. Target.adddress & Target.value are either a single cell or; if selected, a range of cells so a one liner works Sheets("Sheet2").Range(Target.Address).Value = Target.Value Mike "joel" wrote: When using a worksheet change if somebody copies a group of cells you have to run the code for every values in the group like this Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False for each cell in Tartget changedCell = cell.Address Sheet2.Range(changedCell).Value = cell.Value next Cell Application.EnableEvents = True End Sub I'm not sure if this code will work because worksheet change is inteneded only to modify the cells on the active sheet and doesn't alows other cells to get changed. The Delkte key may not work because the worksheet change event occurs after the cell is changed and if somebody removed data from the cell you can't copy the data to another sheet. You could use the function Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Which will occur before the user changes the cell data. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161923 Microsoft Office Help . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with sheets that mirror each other
The code I gave you a while back and the code given by this thread will only
work when cells are ClearContents. When you delete cells from sheet1, you are shifting the remaining cells to a new position. The code uses the range that is actually selected on sheet1 for example A1:A100, and makes sheet2 range A1:A100 the same values. If you selected Shift Cells Up, then on sheet1, cell A101 is now in A1 and so on down the column. But in sheet2, all that happened is that range A1:A100 equals what is now in that range on sheet1, the rest of the cells in the column did not Shift Up, so sheet2 A101 is the same as it was, and so on down from there. I am not sure you are going to satisfactorily get what you want from this approach. Since the other sheet is hidden, perhaps using another event to make one sheet mirror the other such as the Activate/Deactivate event. Mike F "Robert Crandal" wrote in message ... I have TWO different layouts, a red and blue layout, each with slightly different dimensions. It gives the user 2 different ways of instantly looking at the data. When the red sheet is toggled on, I hide the blue sheet and the unhide the red sheet (or vice versa). So, you see, the user only sees one sheet at a time, and I want them to have similar data at all times. My mirror plan works except for case when a block is highlighted and then deleted with the "Delete" key, as the deleted cells dont all seem to copy over for some reason. Got any ideas? "OssieMac" wrote in message ... Hi Robert, Perhaps you would like to share the reason for mirroring the 2 sheets. There might be a more efficient way of achieving this like copying the sheet before close or save etc. -- Regards, OssieMac "Robert Crandal" wrote: I'm trying to setup that mirror each other at all times. I am currently handling "Worksheet_Change()" in my sheets, so any time one sheet gets modified, I am trying to copy the contents of one sheet to the other. The shortened code looks as follows: ' ' Sheet 1 ' Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value End Sub ' ' Sheet 2 ' 'Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet1.Range(changedCell).Value = Sheet2.Range(changedCell).Value End Sub This code generally works when a user changes one cell at a time. However, (for example) if a user selects a big block of cells on Sheet1 and presses the DELETE key, that block of cells will actually delete on Sheet1, but only one (or two or so) cells will delete from Sheet2. How can I modify the subroutines above to handle cases when a block of data is highlighted in one sheet, then tell the second sheet to delete all those cells as well?? Thank you!!! . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with sheets that mirror each other
As an idea would the followng approach do what you want??
'sheet 1 Private Sub Worksheet_Change(ByVal Target As Range) Dim rng1 As Range Dim rng2 As Range Application.EnableEvents = False Sheets(2).Cells.ClearContents Set rng1 = Sheets(1).UsedRange Set rng2 = Sheets(2).Range("A1") rng1.Copy rng2 Application.EnableEvents = True End Sub 'sheet 2 Private Sub Worksheet_Change(ByVal Target As Range) Dim rng1 As Range Dim rng2 As Range Application.EnableEvents = False Sheets(1).Cells.ClearContents Set rng1 = Sheets(2).UsedRange Set rng2 = Sheets(1).Range("A1") rng1.Copy rng2 Application.EnableEvents = True End Sub -- jb "Robert Crandal" wrote: I have TWO different layouts, a red and blue layout, each with slightly different dimensions. It gives the user 2 different ways of instantly looking at the data. When the red sheet is toggled on, I hide the blue sheet and the unhide the red sheet (or vice versa). So, you see, the user only sees one sheet at a time, and I want them to have similar data at all times. My mirror plan works except for case when a block is highlighted and then deleted with the "Delete" key, as the deleted cells dont all seem to copy over for some reason. Got any ideas? "OssieMac" wrote in message ... Hi Robert, Perhaps you would like to share the reason for mirroring the 2 sheets. There might be a more efficient way of achieving this like copying the sheet before close or save etc. -- Regards, OssieMac "Robert Crandal" wrote: I'm trying to setup that mirror each other at all times. I am currently handling "Worksheet_Change()" in my sheets, so any time one sheet gets modified, I am trying to copy the contents of one sheet to the other. The shortened code looks as follows: ' ' Sheet 1 ' Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value End Sub ' ' Sheet 2 ' 'Private Sub Worksheet_Change(ByVal Target As Range) changedCell = Target(1).Address(0, 0) Sheet1.Range(changedCell).Value = Sheet2.Range(changedCell).Value End Sub This code generally works when a user changes one cell at a time. However, (for example) if a user selects a big block of cells on Sheet1 and presses the DELETE key, that block of cells will actually delete on Sheet1, but only one (or two or so) cells will delete from Sheet2. How can I modify the subroutines above to handle cases when a block of data is highlighted in one sheet, then tell the second sheet to delete all those cells as well?? Thank you!!! . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
synchronizing mutiple sheets to mirror the previous data | Excel Discussion (Misc queries) | |||
sheets that mirror each other | Excel Programming | |||
Mirror Wildcard? | Excel Worksheet Functions | |||
Mirror value to another cell. | Excel Programming | |||
Mirror Workbook | Excel Programming |