Code getting stuck???

[Trueblue862]

Hi I have this piece of code which is activated from a button on my userform, it is used to print off my roster for the next year. It works fine but it gets stuck when it gets to "ws.PrintPreview" I have tried unloading the userform before it gets to that line but then the code errors. It opens the printpreview window just fine but then I can't do anything with excel. It just beeps at me when I try and do anything. There's probably many better ways of doing this, but it's working aside from getting stuck there. Any help would be appreciated.

Private Sub BtnPrint_Click()

iSelected = CbXDaTe.Value
    lR = Cells(Rows.Count, "A").End(xlUp).Row
    Set rData = Range("A2:A" & lR).Find(iSelected, LookIn:=xlValues, lookat:=xlWhole)
    If rData Is Nothing Then: Exit Sub
        ans = rData.Row
        Rows(ans).Resize(52, 9).Select
    
  myPRINT_ONE_PAGE
   
        

End Sub

 
 
Sub myPRINT_ONE_PAGE()

Dim myZoom As Boolean
Dim iWidth As Long
Dim iHeight As Long
Dim TARGET_WS As Worksheet
Dim myPRINT_RANGE As Range

myZoom = False
iWidth = 1
iHeight = 1

Set ws = Sheets("Personal Roster")

On Error Resume Next
  

Set myPRINT_RANGE = Range("B1:K225")

 On Error GoTo 0
 If myPRINT_RANGE Is Nothing Then
 MsgBox "Code will abort - nothing selected"
 Exit Sub
 End If

 With ws.PageSetup
 
 .Zoom = False
 .PrintArea = Selection.Address
 .CenterHorizontally = True
 .CenterVertically = False
 .Orientation = xlPortrait
 .FitToPagesWide = iWidth
 .FitToPagesTall = iHeight
    
 End With

ws.PrintPreview


theEND:
Set ws = Nothing

 End Sub

 

[mole999]

as you are printing do you actually need to view before it spools or maybe activesheet.printpreview

 

[Trueblue862]

I need to get to a point where I can adjust printer settings if necessary. I wonder if I would have the same issues if I just opened the print dialog box.

 

[Logit]

I agree with mole .... Print Preview standard is to perform prior to printing.

 

[mole999]

I need to get to a point where I can adjust printer settings if necessary. I wonder if I would have the same issues if I just opened the print dialog box.

I'm sure I have seen somewhere with print preview causing issues

 

[Fluff]

Try setting the the userform ShowModal property to false

 

[Trueblue862]

Try setting the the userform ShowModal property to false

Thanks Fluff, works perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback.