Some macros not working on shared workbook

[KevHardy]

Hi all,
I'm sharing a workbook but one of the macros isn't working:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Const WS_RANGE As String = "J:J"
Dim rng1 As Range
Dim rng2 As Range

If Target.Cells.Count > 1 Then
Exit Sub
End If

If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Exit Sub
End If

Set rng1 = Target.EntireRow.Range("A1:J1")

With Worksheets("outcomes")
Set rng2 = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Value <> "" Then
With rng1
..Copy _
Destination:=rng2
..Delete Shift:=xlUp
End With
End If
ws_exit:
Application.EnableEvents = True

End Sub

This one copy/deletes a defined row of data to another sheet when J:J
changes (it has a validation list of blank or yes).
I have a few other macros in the workbook such as opening at a specific
sheet, highlighting selected rows, two useforms adding data to the next
available empty row etc and these all work fine.

Any ideas why the one above doesn't?

 

[Dave Peterson]

It worked for me in my simple testing.

Did you allow macros to run when you opened the file?
Did you put the code in the correct worksheet module?

 

[KevHardy]

The code is actually under 'Microsoft Excel Objects' - Sheet1 (Allocations)
The code moves a specified cell range to Sheet2 and deletes in from sheet one.
I also have a slightly different code on Sheet2 (Outcomes) which performs a
similar (but not identical) move to sheet3 (Archive).
So the code contained in the actual sheets fails to trigger. I didn't
realise - should it be in separate modules then?

 

[Dave Peterson]

It sounds like you put them in separate modules already.

One version of the code is in the worksheet module for Allocations and the other
version of the code is in the worksheet module for Outcomes, right?

If that's true, then the events should be firing--but only if you allowed macros
to be enabled when you open the workbook.

My other guess is that you turned off .enableevents (either in code) and failed
to turn it back on.

You could have done this by accident while testing. If you interrupted the code
after it was set to false, but before it was set back to true, you'll see this
problem.

You can close excel and reopen it (and your workbook) or you can:
Open the VBE
Hit ctrl-g to see the immediate window
type:
application.enableevents = true
and hit enter.

If you interupted the code, then this would be a one-time fix (or each time you
interrupt that code). But if you have other code that turned .enableevents off,
you'll want to find why it's not being reset.

 

[KevHardy]

Hi Dave,
The application.enableevents = true works up to a point I placed it in
each of the codes and they now copy the row data to the next sheet but don't
delete the old data for some reason, so there must be a problem with the code
I think?

As for your other questions:
Q. One version of the code is in the worksheet module for Allocations and
the other
version of the code is in the worksheet module for Outcomes, right?
A. Yes
Q. If that's true, then the events should be firing--but only if you allowed
macros
to be enabled when you open the workbook.
A. Yes. Opening the workbook brings up the message box about allowing macros
and I say yes please.

It's very frustrating as the thing works perfectly until I share it and then
only one of the codes won't work.

 

[Dave Peterson]

I think that your code is deleting stuff exactly the way your code says--go to
the bottom of your range and you'll see that the rows have been messed up.

The data in columns A:I have shifted up. But the data in columns J:xxx haven't
been deleted.

These lines of the code delete rng1:

Set rng1 = Target.EntireRow.Range("A1:I1")

But this line of code:
Set rng1 = Target.EntireRow.Range("A1:I1")
sets the range to delete to be only the first 9 columns of that row.

I take it that this is not what you want.

If you wanted to delete the entire row in the original worksheet:

If that's not what you wanted, what do you want?

 

[KevHardy]

Hi Dave,
The A1:I1 is correct. I originally had it copy/deleting the entire row using
entirerow.Delete but when it did this it was overwitting some validation
(drop down boxes) that appears on the next sheet in columns K and L. Setting
it to copy/delete just columns A to I prevented it from doing this.

The macro works perfectly until the workbook is shared and I can't see why

 

[Dave Peterson]

The only (potential) problem I saw was that the entire row wasn't deleted.

I couldn't reproduce any problem if that wasn't what you meant.

 

[KevHardy]

Hi Dave,
Thanks for trying anyway
I think I might try to find a way of writing the code a different way to see
if that works. If I ever solve this I'll update this thread.
Thanks again
Kev