PDA

View Full Version : Before Print Issue



zoom38
06-30-2015, 05:51 AM
Good morning, I have a sheet that is set to view at 100%. This sheet is set to 95% when printing which skews a checkbox and moves it out of place. My solution was to move the checkbox to the correct position when working on the sheet using worksheet activate which works perfectly. Then I put code into the before print event to move the checkbox to the appropriate position when printing. This however ONLY works when I put a break in the code. Without the break it doesn't move the checkbox before printing. I tried adding a pause but that didn't do the trick.

Here is the worksheet activate code:


Private Sub Worksheet_Activate()
Application.ScreenUpdating = False

'Moves the checkbox to a position so it doesn't cover text.
With Me.Shapes("CheckBox1")
.Height = 20.25
.Left = 525
.Top = 21
.Width = 16.5
.Placement = xlMoveAndSize
End With

Call SortRoutine1
Range("J3").Select
Application.ScreenUpdating = True
End Sub


This is the before print event that only works when I add a break before the sub completes.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
With sheets("205A").CheckBox1
.Height = 20.25
.Left = 536
' Application.Wait Now + TimeValue("00:00:05")
.Top = 21
.Width = 16.5
.Placement = xlMoveAndSize
End With
End Sub


Can someone advise why it doesn't work without a break and advise how to correct it?

Thanks
Gary

SamT
06-30-2015, 07:09 AM
Can someone advise why it doesn't work without a break
Sorry, no.

advise how to correct it?
:dunno, try this. It might be faster(?).


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Static ShtResized As Boolean

If ShtResized Then 'Print now
ShtResized = False
Exit Sub
End If

Cancel = True 'Don't print yet
ShtResized = True

With sheets("205A").CheckBox1
.Height = 20.25
.Left = 536
.Top = 21
.Width = 16.5
.Placement = xlMoveAndSize
End With
WorkBook.PrintOut 'Print now
End Sub

zoom38
06-30-2015, 11:06 AM
Thanks for making the attempt Sam, I couldn't get it to consistently work so I got rid of the checkbox and just used a cell as a checkbox in its place.

Gary