Some macros not working on shared workbook

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?
0
Utf
2/5/2010 10:22:01 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
1543 Views

Similar Articles

[PageSpeed] 47

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 wrote:
> 
> 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
0
Dave
2/5/2010 2:04:36 PM
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" wrote:

> 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 wrote:
> > 
> > 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
> .
> 
0
Utf
2/5/2010 3:03:02 PM
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 wrote:
> 
> 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" wrote:
> 
> > 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 wrote:
> > >
> > > 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
> > .
> >

-- 

Dave Peterson
0
Dave
2/5/2010 3:13:40 PM
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" wrote:

> 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 wrote:
> > 
> > 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" wrote:
> > 
> > > 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 wrote:
> > > >
> > > > 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
> > > .
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
2/5/2010 4:10:03 PM
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")

> > > > > With rng1
> > > > >    ...
> > > > >    ...
> > > > >    .Delete Shift:=xlUp
> > > > > End With

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:

> > > > > With rng1
> > > > >    ...
> > > > >    ...
> > > > >    .entirerow.Delete
> > > > > End With

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

KevHardy wrote:
> 
> 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" wrote:
> 
> > 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 wrote:
> > >
> > > 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" wrote:
> > >
> > > > 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 wrote:
> > > > >
> > > > > 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
> > > > .
> > > >
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
2/5/2010 4:30:14 PM
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" wrote:

> 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")
> 
> > > > > > With rng1
> > > > > >    ...
> > > > > >    ...
> > > > > >    .Delete Shift:=xlUp
> > > > > > End With
> 
> 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:
> 
> > > > > > With rng1
> > > > > >    ...
> > > > > >    ...
> > > > > >    .entirerow.Delete
> > > > > > End With
> 
> If that's not what you wanted, what do you want?
> 
> KevHardy wrote:
> > 
> > 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" wrote:
> > 
> > > 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 wrote:
> > > >
> > > > 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" wrote:
> > > >
> > > > > 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 wrote:
> > > > > >
> > > > > > 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
> > > > > .
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
2/7/2010 11:15:01 AM
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 wrote:
> 
> 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" wrote:
> 
> > 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")
> >
> > > > > > > With rng1
> > > > > > >    ...
> > > > > > >    ...
> > > > > > >    .Delete Shift:=xlUp
> > > > > > > End With
> >
> > 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:
> >
> > > > > > > With rng1
> > > > > > >    ...
> > > > > > >    ...
> > > > > > >    .entirerow.Delete
> > > > > > > End With
> >
> > If that's not what you wanted, what do you want?
> >
> > KevHardy wrote:
> > >
> > > 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" wrote:
> > >
> > > > 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 wrote:
> > > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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 wrote:
> > > > > > >
> > > > > > > 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
> > > > > > .
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > > .
> > > >
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
2/7/2010 1:38:09 PM
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

"Dave Peterson" wrote:

> 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 wrote:
> > 
> > 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" wrote:
> > 
> > > 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")
> > >
> > > > > > > > With rng1
> > > > > > > >    ...
> > > > > > > >    ...
> > > > > > > >    .Delete Shift:=xlUp
> > > > > > > > End With
> > >
> > > 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:
> > >
> > > > > > > > With rng1
> > > > > > > >    ...
> > > > > > > >    ...
> > > > > > > >    .entirerow.Delete
> > > > > > > > End With
> > >
> > > If that's not what you wanted, what do you want?
> > >
> > > KevHardy wrote:
> > > >
> > > > 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" wrote:
> > > >
> > > > > 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 wrote:
> > > > > >
> > > > > > 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" wrote:
> > > > > >
> > > > > > > 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 wrote:
> > > > > > > >
> > > > > > > > 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
> > > > > > > .
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > > .
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
2/8/2010 7:39:01 AM
Reply:

Similar Artilces:

Chart- View Labels- Shared Database...
I am working out of a database used by two other individuals. When I attempt to print a report, my chart does not display the labels. If I open the chart in design view, I am unable to view the labels, but if I go further and actually open the chart all information is displayed. The other individuals working in the database are able to readily review the reports and print the data without any problem. We are all using the Access version, and I have already installed the Office Service Pack 3 fix. Nothing seems to working. Any assistance is greatly appreciated. ...

Rules not working after org rename
We are upgrading from exchange 5.5 to 2k3. As a part of that we needed to change the Org name to get rid of invalid characters. After the rename rules don't work. We're now up and running on 2k3, but rules still don't work. Does anyone have any idea's on twhat to try to get rules working again? "Phil Lawson" <PhilLawson@discussions.microsoft.com> wrote: >We are upgrading from exchange 5.5 to 2k3. As a part of that we needed to >change the Org name to get rid of invalid characters. After the rename rules >don't work. We're now up ...

Print Macro #4
Hello I would like to write a macro, for a particular workbook, which is activated when you select File/Print/OK (the macro will check to see if certain conditions are met and will print if they are but will return an error message if they are not). Can anyone suggest the code required? Thanks in advance Nick There's a workbook event that you can tie into. Rightclick on the excel icon (to the left of the File option on the worksheet menu bar). Select view code and paste this in the code window: Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) If LCase(Me.Wo...

running macros #2
Hi, I have created a macro that imports data, sorts it, and delimits it. The macro in itself works just fine, however trying to run it a second time on a different file causes it to blow up. Prior to importing the data, I clear out the old contents so it doesn't prompt me to overwrite, the re-run of the macro WILL prompt me, and rather than overwrite, it appears to insert instead, and therby causes it to trash the existing data. I'd sure appreciate any help here. Thx. Without seeing your code, it's nearly impossible to tell. In article <58725910-CF96-4589-8CF0-76ADF515E2...

Why dose my desk top alert not work
Heloo I cant get my desktop alert to work, have checked all the ticks in email options and they are ticked, It workes manuall when clicking send/recieve Your help is graetfully recieved spursman ...

Rules that don't work-OL2007
Is MS ever going to put out a version of OL in which rules actually work? I just upgraded to Office OL2007 from Office OL2003, thinking that one of the benefits would be that my rules would start doing what they're supposed to. Hmm. I have a rules that work fine if I see that a subject message has gotten through, and I manually run the rule, but the rule(s) don't do their stuff automatically when the message arrives. I've been very careful about ordering the rules, so I'm certain that the rules are executed in the correct order. Doesn't matter. Are OL rules just a...

How do I assign a shortcut key to an existing macro in Excel 2003.
ntahall, Alt+F8 to bring up the choose macro box, select your macro, options, assign a shortcut from there -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "ntahall" <ntahall@discussions.microsoft.com> wrote in message news:21B6102C-4B88-4131-A9AC-C48879896A17@microsoft.com... > ...

Macros to select multiple sheets to make simultaneous change
Hi, I need to have a macro such that it'll select all sheets from the first to the sheet before a sheet called "test". After selecting these sheets, i want to make simultaneous changes to them. So how do I add the code in the macro to choose all these sheets? I can't put the names of the sheets coz the names changes often. THanks! noel Hi noel This will loop through the sheets Sub test() Dim num As Integer Dim a As Integer num = Sheets("test").Index - 1 For a = 1 To num With Sheets(a) '.Select MsgBox .Na...

Shared Excel filed locked by another user.
A couple of our users have problem to take over "write" access to a shared Excel file. While other users are notified that the user holding "write" access has closed the file so they can start editing the file, these two people are still getting message that the file is locked by that previous user who already closed the file. I know detail view on Windows explorer to the folder can cause the issue but that is not the case for me either. I have closed all windows explorer from the previous user. I appreciate your help in advance. Jonathan Kim ...

memory sharing between threads
I got two threads, thread A and thread B. Both of them were running. Now, before thread B is going to terminate, it creates a file mapping object and writes something in it, then PostThreadMessage() to thread A, and thread B terminates. On receiving the thread message from thread B, thread A wants to open the file mapping object and read in data. But, unfornuately, it's already closed when thread B terminates. I know SendMessage() wouldn't return until the message is processed. But it requires a window handle of thread A. I just couldn't pass a handle between two independen...

Not sure why this code isn't working
Hello, I have a subform in Access 2003 with these fields, type of day, OLPEndDate, OLPBeginDate. At the end of each row, I want to calculate the total day taken for that particular vacation request, so if a person took 12/1/2009-12/2/2009 then the last field "TotalOLPTaken" should calculate as 1. What's happening is that it does calculate but all of the rows calculate to the same number. If I put the cursor in the second row and that row happens to have 12/4/2009-12/6/2009, then even the row with 12/1-12/2 calculates as 2. Then if I put the cursor back in the fi...

Place a button on a worksheet and assign a macro to it?
How do I place a single "button" on a worksheet (say, in one cell), and assign a macro to it? Hi, Choose View, Toolbars, and pick the Forms toolbar. Click the Button tool and then click in the spreadsheet. You will be prompted for the macro you want to assign. You can resize and move the button after assigning the macro. If this helps, please click the Yes button Cheers, Shane Devenshire "PFB" wrote: > How do I place a single "button" on a worksheet (say, in one cell), and > assign a macro to it? I often use shapes from the drawing tool bar. O...

Office need to install VBA macro language support
In Office 2003, I get the messaged that Word needs to install VBA language support every time I start Word. I get the message even after I run the installer. How do I fix that? You need to run Setup again and select what you need to install: not everything is installed with a standard install. Select Advanced and scroll through the list - expanding as required - and when you find the VBA section, right-click and choose Install on My PC. -- Terry Farrell - MSWord MVP "Hoosier Texas" <Hoosier Texas@discussions.microsoft.com> wrote in message news:F7937647-...

Mac Office 2004 working with Office on WinXP
I have just started business school and most people here are using = Office on=20 new IBM thinkpads installed with Office. I have run into a problem = where all=20 the files (so far Office and Powerpoint) are not recognized by my = version of=20 Office 2004 (for OSX). Has anyone run into this problem? I know they = are=20 just using the basic version of Office on Windows and not doing anything = fancy, I have to think they should be compatible. =20 -Just to clear up any questions they sent a file with a .doc ending so = that=20 should be it.=20 - And also when they sent it using an older ...

Need to add something to the end of an existing macro to delete a worksheet #2
I have a macro that will clean up an Excel version of a Bill of Materials that gets exported from another software (SQL) from our MRP system. I would like to add something to the end of the macro to tell it to delete the "sheet1" worksheet, because the macro I've created automatically creates a new sheet 2 that I want to replace it. The message box that automatically appears (for which I want the existing macro to take care of and not have show up) says "Data may exist in the sheet(s) selected for deletion. To permanently delete the data press Delete." Any advice wo...

Macro functions on exit
I have a lot of workbook macros that hide toolbars and change othe default settings on startup. It's annoying to close my program an open up another excel document and have to restore all my settings. I there a way to execute a set of functions with an OnClose call o something? I basically need to run a macro when they x-out of my program t restore their settings. Thanks in advance for any ideas -- Message posted from http://www.ExcelForum.com Hi have a look at the workbook event 'BeforeClose' to restore your settings. See http://www.cpearson.com/excel/events.htm >---...

Business Unit Admins Can't view Account sharing information
Hi, We have multiple regions in our company set up as Business Units. Each Business Unit has a user with an admin role that has just about unlimited rights within their respective BU. These admins can share accounts just fine but can't see the existing sharing information (i.e. what users an account is already shared with). I, as the company system administrator, can see all the sharing information without a problem. I can send screen shots as necessary ...

Preview/Setup not working
The Setup button in Print Preview isn't working on any of my spreadsheets. I have Excel 9.0.6926 SP-3, and have repaired/reinstalled and updated. Everything else is fine, but in case it's relevant I'm also unable to get into safe mode and unable to defrag. Suggestions very gratefully received. For some of the options, this is always the case. I didn't check them all, but I think it is only Rows and Columns to repeat at top -- Kind Regards, Niek Otten Microsoft MVP - Excel "bagwash" <bagwash@ntlworld.com> wrote in message news:QgGXb.744$O%4.714@newsfep3-...

Conditional formatting not quite working
I have a form that is generated by a query. The query is populated via table. I want all values in the report that are over 1.99% to show up as red. Those values at 1.99% and lower should show up as green. IOW, percent values from 0 to 1.99% are good values and show green. Anything at 2% and over is bad, and should show up as red. I've created conditinal formatting in several different ways, using the options available under conditional formatting. It seems that no matter what I select, greater than, equal to, etc, all the values change to whatever the cond format is suppos...

open multiple workbooks at startup
Ever since i ran a macro that opened several workbooks, my Excel always opens the same workbooks. I have searched and deleted all the files that are being opened. Search shows they are in a sub-folder "Application Data", but this folder is not listed in explorer and the files that are opened are not found with a file search. another thing, the open files in Excel options is cleared to blank. How do I stop opening these files when I open another file? Dear Ron, you can find the folder application data if you switch on the view hidden files option in windows it will be in C:...

PLEASE HELP!!!!! MAcro Emergency
maybe now someone will help me?? noone has responded to my last 2 post so I am trying to figure it out myself, am trying to build a macro an need to select RELATIVE REFERENCE before I record it, that "sto recording" toolbar was there the first time i tried to record the macr with the button to select relative reference but now every time i try t record the macro that toolbar isnt there anymore and the macro wont wor unless i can first select relative reference, please help before i tak the sledge to this computer PLEASE!! -- RalphS -------------------------------------------------...

INDIRECT formula not working
Hi, What am I doing wrong here... This formuals does not work: =INDIRECT("'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1") where A & B are cell references used in the naming of many worksheets I am attempting to obtain the value in Cell Q1 of worksheets: YUNUS-A3-B3 GD0 YUNUS-A4-B4 GD0 YUNUS-A5-B5 GD0 YUNUS-A6-B6 GD0 YUNUS-A7-B7 GD0 YUNUS-A8-B8 GD0 On Thu, 8 Mar 2012 22:27:39 -0000, Yunus <yunus786@btopenworld.com> wrote: > >Hi, > >What am I doing wrong h...

Help required on working with date
Hi I have dates in a column in dd-mmm-yy format, like 29-Mar-04 15-Apr-04 13-May-04 I want to put in the column adjecent to this date column the date starting with 1st day of each month like - 01-Mar-04 01-Apr-04 01-May-04 How can this be done by use of formula? I tried but do not get any clue to go futher. -Shanks Hi try =DATE(YEAR(A1),MONTH(A1),1) -- Regards Frank Kabel Frankfurt, Germany Shanks wrote: > Hi > > I have dates in a column in dd-mmm-yy format, like > > 29-Mar-04 > 15-Apr-04 > 13-May-04 > > I want to put in the column adjecent to this da...

:Workbook Merge Fails with a Temporal-like error
All of the basic settings are fine because simple merges are working. When I make one change is Spreadsheet A, and Merge those changes into Spreadsheet B, everything works fine. However, when I make changes in Spreadsheet A, and another user makes changes in Spreadsheet B (which is an exact copy of Spreadsheet A), I get the following error: ------ That workbook cannot be merged because it contains changes that have not been merged followed by later changes that have already been merged into the current workbook. ------ Any ideas? Never mind, I found the solution. - techgroups &...

Two users sharing a computer but not inbox
Hi guys, is it possible, in Outlook 02, for two users to share a computer but be able to send/receive emails from different accounts? Eg: in our restaurant we have one computer. Currently that computer is receiving mail for dine@restaurant but now we want the chef to have his own address, chef@restaurant. However, we want dine@ and chef@ to go to separate inboxes so the manager and chef don't have to sift through unnecessary messages to read their own mail. I have tried setting up the new email address and telling it to put messages in a new data file called chef.pst but this hasn't...