Paige
12/18/2006 9:34:00 PM
Jim, it is not secret, and thanks so much for the offer. Where do I send it
to?
"Jim Thomlinson" wrote:
> I am starting to run out of possibilities... Do you have selection change
> code that might be running??? If the info in the sheet is not super secret
> you could e-mail me a copy and I will take a look at it for you...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Paige" wrote:
>
> > Jim, sorry; there are no calcs in the worksheet; is only text. This is
> > SOOOOOO frustrating.
> >
> > "Jim Thomlinson" wrote:
> >
> > > You can try disabling the calculations. Depending what your spreadsheet looks
> > > like that could be causing the long execution...
> > >
> > > Application.Calculation = xlCalculationManual
> > > ...
> > > Application.Calculation = xlCalculationAutomatic
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Paige" wrote:
> > >
> > > > Thanks, Jim and Tom. I made the change re the borders; appreciate knowing
> > > > that! Also tried disabling events, but that didn't make any difference
> > > > unfortunately. Got any other ideas? Obviously I've messed something up in
> > > > the code.
> > > >
> > > > "Jim Thomlinson" wrote:
> > > >
> > > > > Everything after this line appears to have events enabled and there are
> > > > > potentially changes being made...
> > > > >
> > > > > ' New Entry
> > > > >
> > > > > Am I missing something...?
> > > > > --
> > > > > HTH...
> > > > >
> > > > > Jim Thomlinson
> > > > >
> > > > >
> > > > > "Tom Ogilvy" wrote:
> > > > >
> > > > > > I couldn't get any recursive calls to the change event. Where did you see
> > > > > > it?
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > >
> > > > > > "Jim Thomlinson" wrote:
> > > > > >
> > > > > > > It looks like you might have a problem with leaving events enabled in that
> > > > > > > section of code. Your change event seems to be making changes which will
> > > > > > > make for a recursive procedure (The change events makes a change which in
> > > > > > > turn calls the change event). Try disabling events at the very beginning of
> > > > > > > the procedure and then re-enable them at the very end. A couple of things to
> > > > > > > speed up your code would also be
> > > > > > >
> > > > > > > Selection.Borders.LineStyle = xlNone
> > > > > > > Selection.BorderAround xlContinuous, xlThin
> > > > > > >
> > > > > > > --
> > > > > > > HTH...
> > > > > > >
> > > > > > > Jim Thomlinson
> > > > > > >
> > > > > > >
> > > > > > > "Paige" wrote:
> > > > > > >
> > > > > > > > The purpose of this code is to update another worksheet ('Change History')
> > > > > > > > any time a cell is changed or a row/column added/deleted on the main sheet
> > > > > > > > (entitled 'Checklist'). Problem is that when I add/delete a row/column, the
> > > > > > > > worksheet locks up for awhile, even though the code has finished running. It
> > > > > > > > doesn't update the 'Change History' sheet until I go over to the code window
> > > > > > > > and then back to the spreadsheet, or until several minutes of just waiting.
> > > > > > > > Can someone see if they know why this is happening? Note: The code runs
> > > > > > > > fine if I take out the references to the rows/columns, or am just changing an
> > > > > > > > individual cell.
> > > > > > > >
> > > > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > > > > Dim SourceRange As Range
> > > > > > > > Dim str As String
> > > > > > > > Dim myString As String
> > > > > > > >
> > > > > > > > Set SourceRange = Worksheets("Change
> > > > > > > > History").Range("D65536").End(xlUp).Offset(0, -3)
> > > > > > > >
> > > > > > > > If Not (Intersect(Target, Range("A1:AW65536")) Is Nothing) Then
> > > > > > > > Application.ScreenUpdating = False
> > > > > > > > With Worksheets("Change History")
> > > > > > > > .Activate
> > > > > > > > .Range("A65536").End(xlUp).Offset(1, 0).Select
> > > > > > > > ' Asks if new version or not
> > > > > > > > Do
> > > > > > > > str = InputBox("Is this a new version?")
> > > > > > > > If str = "y" Then
> > > > > > > > Worksheets("Change
> > > > > > > > History").Range("D65536").End(xlUp).Offset(0, -3).AutoFill
> > > > > > > > Destination:=Worksheets("Change History").Range(SourceRange,
> > > > > > > > SourceRange.Offset(1, 0)), Type:=xlFillDefault
> > > > > > > > Exit Do
> > > > > > > > End If
> > > > > > > > If str = "n" Then
> > > > > > > > Worksheets("Change
> > > > > > > > History").Range("D65536").End(xlUp).Offset(0, -3).Select
> > > > > > > > Selection.Copy
> > > > > > > > Worksheets("Change
> > > > > > > > History").Range("D65536").End(xlUp).Offset(1, -3).Select
> > > > > > > > ActiveSheet.Paste
> > > > > > > > Exit Do
> > > > > > > > End If
> > > > > > > > Loop
> > > > > > > > ' Cell Changed
> > > > > > > > If Target.Address <> Target.EntireColumn.Address And Target.Address <>
> > > > > > > > Target.EntireRow.Address Then
> > > > > > > > Application.EnableEvents = False
> > > > > > > > Worksheets("Change History").Range("A65536").End(xlUp).Offset(0,
> > > > > > > > 1).Activate
> > > > > > > > ActiveCell.Value = Target.Address
> > > > > > > > Application.EnableEvents = True
> > > > > > > > End If
> > > > > > > > If Target.Address = Target.EntireRow.Address Then
> > > > > > > > Application.EnableEvents = False
> > > > > > > > Worksheets("Change History").Range("A65536").End(xlUp).Offset(0,
> > > > > > > > 1).Activate
> > > > > > > > ActiveCell.Value = Target.EntireRow.Address
> > > > > > > > Application.EnableEvents = True
> > > > > > > > End If
> > > > > > > > If Target.Address = Target.EntireColumn.Address Then
> > > > > > > > Application.EnableEvents = False
> > > > > > > > Worksheets("Change History").Range("A65536").End(xlUp).Offset(0,
> > > > > > > > 1).Activate
> > > > > > > > ActiveCell.Value = Target.EntireColumn.Address
> > > > > > > > Application.EnableEvents = True
> > > > > > > > End If
> > > > > > > > ' New Entry
> > > > > > > > ActiveCell.Offset(0, 1).Activate
> > > > > > > > If Target.Address = Target.EntireRow.Address Or Target.Address =
> > > > > > > > Target.EntireColumn.Address Then
> > > > > > > > ActiveCell.Value = "N/A"
> > > > > > > > ActiveCell.Select
> > > > > > > > Else
> > > > > > > > Target.Copy
> > > > > > > > Selection.PasteSpecial Paste:=xlPasteFormats,
> > > > > > > > Operation:=xlNone, _
> > > > > > > > SkipBlanks:=False, Transpose:=False
> > > > > > > > ActiveCell.Value = Target.Value
> > > > > > > > ActiveCell.Value = Target.Value
> > > > > > > > End If
> > > > > > > > ' Formats new entry
> > > > > > > > ActiveCell.Select
> > > > > > > > With Selection
> > > > > > > > .Interior.ColorIndex = 2
> > > > > > > > .HorizontalAlignment = xlLeft
> > > > > > > > .NumberFormat = "General"
> > > > > > > > .Font.Name = "Arial"
> > > > > > > > .Font.FontStyle = "Regular"
> > > > > > > > .Font.Size = 10
> > > > > > > > End With
> > > > > > > > Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> > > > > > > > Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> > > > > > > > With Selection.Borders(xlEdgeLeft)
> > > > > > > > .LineStyle = xlContinuous
> > > > > > > > .Weight = xlThin
> > > > > > > > .ColorIndex = 15
> > > > > > > > End With
> > > > > > > > With Selection.Borders(xlEdgeTop)
> > > > > > > > .LineStyle = xlContinuous
> > > > > > > > .Weight = xlThin
> > > > > > > > .ColorIndex = 15
> > > > > > > > End With
> > > > > > > > With Selection.Borders(xlEdgeBottom)
> > > > > > > > .LineStyle = xlContinuous
> > > > > > > > .Weight = xlThin
> > > > > > > > .ColorIndex = 15
> > > > > > > > End With
> > > > > > > > With Selection.Borders(xlEdgeRight)
> > > > > > > > .LineStyle = xlContinuous
> > > > > > > > .Weight = xlThin
> > > > > > > > .ColorIndex = 15
> > > > > > > > End With
> > > > > > > > ' Date Changed
> > > > > > > > ActiveCell.Offset(0, 1).Activate
> > > > > > > > ActiveCell.Value = Now()
> > > > > > > > ActiveCell.NumberFormat = "mm/dd/yy"
> > > > > > > > ' Who Changed
> > > > > > > > ActiveCell.Offset(0, 1).Activate
> > > > > > > > ActiveCell.Value = Application.UserName
> > > > > > > > ' Description
> > > > > > > > ActiveCell.Offset(0, 1).Activate
> > > > > > > > Do
> > > > > > > > str = InputBox("Please enter a description of what changed.")
> > > > > > > > If str <> "" Then
> > > > > > > > ActiveCell.Value = str
> > > > > > > > Exit Do
> > > > > > > > End If
> > > > > > > > Loop
> > > > > > > > Application.CutCopyMode = False
> > > > > > > > End With
> > > > > > > > Worksheets("Checklist").Activate
> > > > > > > > End If
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > >