Consulting

Results 1 to 2 of 2

Thread: Page setup when selected items are sent to print Preview

  1. #1

    Page setup when selected items are sent to print Preview

    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.

    [CODE=vba]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[/CODE]

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Record a macro of yourself preparing the sheets for one kind of output.
    Stop recording and repeat for the other output kinds.
    In each case, start with everything showing on the sheets so we can pick up on what you hide, also clear the print area before you record each time so we can see what you change in each of the recorded macros.
    I'm guessing that the macro you showed in the last message was just one of the buttons.
    Where the code goes depends a bit on whether your selection of sheets to print out ListBoxSh is a long one (so you only need to pre-process sheets that are going to be printed) or not; or perhaps you have many sheets that are going to be prepared in exactly the same way, in these cases it might be better to have the code inside your For i = loop. Otherwise, if it's just a few sheets, you can put the code in at the beginning of the macro. You might also want to restore some aspects of the sheets (restore hidden columns, remove the display of page breaks etc.) after printing in which case some code needs to go after printpreview and before End Sub.

    If you can attach a workbook with some relevant existing code that would be a huge help.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •