Help with page setup before print preview

[MikeGozna]

Hi Guys
I would like some assistance with this problem please. I am a novice with VBA, so please try to keep it simple if you can.
I have a List box (ListboxSh) on sheet "Home", populated by a series of 'If' statements. (This part works fine.)
I then call a User form"PrintOptions" which holds some check boxes and 3 cmd. buttons, Customers, Admin, and Site. command buttons are coded as below to choose selected sheets from the list box and send them either to 'Print Preview or straight to print. (All this up to here works fine.)
This is the problem, each set of sheets is made up of the same sheets but is formatted differently. i.e. set1 i may be Portrait, Fit to pages wide 1 and zoom 80. it may have columns A to E Hidden =False, Whereas the sheets in set 2, although being the same sheets may be Landscape,Fit to pages wide 1 and Zoom 100. I may also like to Unhide some colums and maybe add some other page setup items.

The code below is what I am doing to get the selected sheets from my list box to my print preview / print screen.

My Question is what code do I need to implement the page setup changes and where in the code do I insert the new code. Please bear in mind that I may need to go backwards and forwards between the three sets of sheets,and therefore the formatting.
Thank you in advance for any help you may be able to give.

Private Sub CommandButton1_Click()
'''''''''''''''''''''''''''''''''''''''''''
'      PRINT PREVIEW OF CUSTOMER SHEETS
''''''''''''''''''''''''''''''''''''''''''''

Application.ScreenUpdating = True
 Unload Me
 
 Dim i As Long, c As Long
Dim SheetArray() As String
    With ActiveSheet.ListBoxSh
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
              ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
            End If
        Next i
        Sheets(SheetArray()).PrintPreview
    
ScreenUpdating = True
  End With
End Sub

 

[JLGWhiz]

Hiding and unhiding rows or columns is not part of the page setup feature and would need to be done either manually or if in code by using If...Then statements with the criteria that determines which rows or columns hide/unhide and when. As for the changes to the sheet print parameters for orientation and zoom, you can do that from the Excel Print Preview window. there is a Page Setup button near the bottom that allows access to those features.

 

[Jon von der Heyden]

Hi Mike

Have you posted this question on any other forums? If you have can you please kindly share with the us the links to those threads so that our members here can see what else has been suggested, or indeed if you receive a solution? We ask this of all members when cross-posting. If you have not cross-posted then please forgive my misunderstanding.

Regards
Jon