P
pano
Hi all, I'm having trouble with this macro, the first two pages print
out really fast but the third page called front1 in the macro, just
hangs for ages before printing. Does any one have any ideas why this
is so.? Is there something in the macro which slows down this page?
I even took the text boxes out of this page Front1 thinking that might
be the reason, but nope did not make a difference.
all ideas appreciated, thanks for taking the time to have a look
Stephen
Sub Front1()
Application.ScreenUpdating = False
'Change cells & txt green & format lines & unprotect sheet
Sheets("back1").Activate
ActiveSheet.Unprotect Password:="123"
Range("I1:J1").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 35
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
' Main print routine
If WorksheetFunction.CountBlank(Sheets("back1A").Range("e5:e40")) = 36
Then
Sheets(Array("MDN1", "back1", "front1")).Select
Else
Sheets(Array("MDN1", "back1", "back1a", "front1")).Select
End If
Sheets("front1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("PrintMenu").Select
'Change colour of cells back again and protect sheet
Sheets("back1").Activate
Range("I1:J1").Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 1
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveSheet.Protect Password:="123"
Range("A5").Select
Sheets("printmenu").Activate
Application.ScreenUpdating = True
End Sub
out really fast but the third page called front1 in the macro, just
hangs for ages before printing. Does any one have any ideas why this
is so.? Is there something in the macro which slows down this page?
I even took the text boxes out of this page Front1 thinking that might
be the reason, but nope did not make a difference.
all ideas appreciated, thanks for taking the time to have a look
Stephen
Sub Front1()
Application.ScreenUpdating = False
'Change cells & txt green & format lines & unprotect sheet
Sheets("back1").Activate
ActiveSheet.Unprotect Password:="123"
Range("I1:J1").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 35
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
' Main print routine
If WorksheetFunction.CountBlank(Sheets("back1A").Range("e5:e40")) = 36
Then
Sheets(Array("MDN1", "back1", "front1")).Select
Else
Sheets(Array("MDN1", "back1", "back1a", "front1")).Select
End If
Sheets("front1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("PrintMenu").Select
'Change colour of cells back again and protect sheet
Sheets("back1").Activate
Range("I1:J1").Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 1
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveSheet.Protect Password:="123"
Range("A5").Select
Sheets("printmenu").Activate
Application.ScreenUpdating = True
End Sub