worksheet change problem 2

[faxof]

hello

i have some worksheet change code on sheet1 that updates corresponding cells on sheet2.

when i add something to sheet1 is gets added to sheet2; when i delete something from sheet1 is gets deleted from sheet2. lovely!

however, when i select a range on sheet1 and delete it doesn't delete the range on sheet2, it only deletes one cell in sheet2

is there a way round this?

quizzed
faxof

 

[xlbo]

dunno - s'pose it'd help if you post the code ;-) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

Hi,

I would guess that you are not referencing Target as a collection...

For Each t in Target
'do yer stuff
Next Skip,
Skip@TheOfficeExperts.com
[URL unfurl="true"]www.TheOfficeExperts.com[/URL]

 

[RogADog]

I doubt whether posting the code will help cos the OnChange event is not activated by deletions. I tried to do this some time ago and gave up!. Only way I can think of (now) is to put some check totals on the two sheets (that produces a sum of the respective critical cells) and then write an OnTime macro to compare the two totals every x seconds. Might need some fancy footwork if the cell values are string though.

Looking forward to seeing some other bright ideas.

Rog

 

[SkipVought]

Hey Ron,

Deletes work on the Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    For Each t In Target
        With t
            Worksheets("Sheet2").Cells(.Row, .Column).Value = .Value
        End With
    Next
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[faxof]

Thanks Geoff
I hope you'll be able to help

Here's the code from sheet2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theRow As Integer
Dim theCol As Integer
Dim changeTxt As String

theRow = Target.Row
theCol = Target.Column
changeTxt = Cells(theRow, theCol).Value

'check for target between L and R

If theCol > 11 And theCol < 19 Then Worksheets(1).Cells(theRow, theCol).Value = changeTxt
End Sub

if i delete something in sheet2 it does delete on sheet1 but not if i select a range and delete it

:'(

faxof

 

[SkipVought]

fax,

here's your code

Private Sub Worksheet_Change(ByVal Target As Range)
    For Each t In Target
        With t
            If .Column > 11 And .Column < 19 Then _
                Worksheets(1).Cells(.Row, .Column).Value = .Value
        End With
    Next
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

To be rigerous, define t as a range...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    For Each t In Target
        With t
            If .Column > 11 And .Column < 19 Then _
                Worksheets(1).Cells(.Row, .Column).Value = .Value
        End With
    Next
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

This should work:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim theRow As Integer
Dim theCol As Integer
Dim changeTxt As String

theRow = Target.Row
theCol = Target.Column
If theCol > 11 And theCol < 19 Then
Select Case Target.Address
Case Selection.Offset(-1, 0).Address, Selection.Offset(1, 0).Address, Selection.Offset(0, -1).Address, Selection.Offset(0, 1).Address
changeTxt = Cells(theRow, theCol).Value
Worksheets(1).Cells(theRow, theCol).Value = changeTxt
Case Else
Selection.Copy Destination:=Sheets(1).Range(Selection.Address)
End Select
Else
Exit Sub
End If
End Sub
Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[SkipVought]

Hello......

Anybody there????

This is NOT a difficult problem.

Target is a RANGE of ONE OR MORE CELLS.

Therefore, you have to treat Target as a COLLECTION. (see above) Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

D'oh - you're correct (of course) but my code still works (and I bet it's marginally quicker for deletions 'cos it does it all in one ;-) ) Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[SkipVought]

Sorry Geoff...

Your procedure did no work if I select rows and clear or drag copy. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Hmmmm - yup - you're right...dammit...I hate being wrong ;-)
I was originally only thinking about data entry / deletions

s'pose that's what comes of using the copy method when I should've realised that target could be a collection. Actually, come to think about it, you can have a star for that info alone Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[SkipVought]

The For...Each technique takes longer - granted -

but..

it accounts for other things like a non-contiguous selection delete

I did try...

    With Target
        .Copy Destination:=Worksheets(1).Range(.Address)
    End With

but it failed on the non-contiguous case
Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Yeh - I was only joking with that timing point - I really don't think it'll make any noticeable difference being as the amount of data being changed in any one go is likely to be small Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde