Excel Crashes after updating workbook from User Form
 

I have a User Form that I use to update several other workbooks. For some
reason after I get done updating any of the workbooks with this code in it,
after it is done updating. When I go to change worksheets in the workbook
being updated excel locks up & I get "Microsoft Office Excel has stopped
working and is
trying find a solution", but it never does.

Each of the other workbooks have 10-15 worksheets each. I can't think of any
reason for a workbook with no code in it to lock up excel when you go to
change from 1 worksheet to another after updating?

This is the code that I narrowed the problem do down to. I narrowed it down
to the code that is commented out is causing the problem. I added a Progress
Bar to the code and thats when the problem started.

Sub Update_Header_Spec()

'not all PageSetup properties are available to all printers
'the On Error Resume Next statement
'ensures that those errors are ignored, allowing
'as many settings to be implemented as possible
'for the particular system in use

'Dim sh As Integer
'Dim totalSheetsToProcess As Integer
'Dim totalSheetsProcessed As Integer
'Dim pctDone As Single

Application.ScreenUpdating = False
' UserForm6.LabelProgress.Width = 0
' UserForm6.EnteringRandomNumbers.Caption = "Updating Engineering Spec..."
' UserForm6.Show
' totalSheetsToProcess = bkSpec.Sheets.Count

On Error Resume Next ' added by JLatham

For sh = 1 To bkSpec.Sheets.Count

With bkSpec.Sheets(sh).PageSetup

.LeftHeader = "&8Cilli Code: " & UserForm1.CLLI_Code_1.Value _
& Chr(10) & "Office Name: " & UserForm1.Office_1.Value

.CenterHeader = "&8TEO Number: " & UserForm1.TEO_No_1.Value _
& Chr(10) & "Supplier Order No: " & UserForm1.CES_No_1.Value

.RightHeader = "&8Page &P of &N" & Chr(10) _
& "Appendix No: " & UserForm1.TEO_Appx_No_2.Value

.CenterFooter = "&8RESTRICTED - PROPRIETARY INFORMATION" _
& Chr(10) & "Not for use or Disclosure outside ATT except under
Written Agreement"

.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.55)
.BottomMargin = Application.InchesToPoints(0.7)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = True
.CenterVertically = True
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""

End With

' totalSheetsProcessed = totalSheetsProcessed + 1
' pctDone = totalSheetsProcessed / totalSheetsToProcess

' With UserForm6
' .FrameProgress.Caption = Format(pctDone, "0%")
' .LabelProgress.Width = pctDone * (.FrameProgress.Width - 10)
' .Repaint

' End With
''The DoEvents statement is responsible for the form updating
' DoEvents

Next sh
'clear the error trap

If Err < 0 Then
Err.Clear

End If
' Unload UserForm6

On Error GoTo 0 ' return to system error handling

End Sub

Excel Crashes after updating workbook from User Form
 

to help isolate the problem I recommend temporarily commenting out the
ON Error statement and changing the Break On error option in VBA menu

1) Tools - Options General - Error Trapping

Set to break on all errors.


2) Go back and run your macro again and see if any other error messae
come up. I suspect there is a hidden error that is getting bypassed by
the ON Error Statement. You need to find and fix the 1st problem which
should resolve the lock up.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=186927

http://www.thecodecage.com/forumz/chat.php