Results 1 to 11 of 11

ListBox to Print Range of Sheets

Threaded View

  1. #1
    Registered User
    Join Date
    05-14-2020
    Location
    Washington State
    MS-Off Ver
    2016
    Posts
    61

    ListBox to Print Range of Sheets

    Hi Everyone,

    I copied the following code from an example I found online. I modified it so that it would work in my workbook. I have the listbox in a userform, rather than in a sheet as in the example I took this from. I have a button on my primary (first) sheet which then calls a userform that contains the list box. Everything is working perfectly except the print preview. When I walk through the code I get a "Run-time error '13': type mismatch" error at the line indicated in my code, below. I'm not real familiar with VBA, so I'm finding examples on line and attempting to modify them in my sheet to get the outcome I desire. I have a basic understanding of this type of error after googling it, but don't know how to fix it. Any help is appreciated. I changed the With statement in the code which originally had "ActiveSheet.ListBoxSh" rather than "PrintBox.ListBoxSh". My userform is called PrintBox, so it seemed appropriate to make this change. I don't know if this is part of my problem or not.

    Also, I can select the sheets I want once the userform pops up, but I then want to link the print/print preview to another button on the userform. I think this will require me to rewrite the "Sheets(SheetArray()).PrintPreview/PrintOut so that they are activated when I click the button(s) on the userform. How would I accomplish this?

    Sub Print_Sheets()
    Dim i As Long, c As Long
    Dim SheetArray() As String
    
        With PrintBox.ListBoxSh
        
            For i = 1 To .ListCount
                If .Selected(i) Then
                    ReDim Preserve SheetArray(c)
                    SheetArray(c) = .List(i)
                    c = c + 1
                End If
            Next i
            
            Sheets(SheetArray()).PrintPreview  - "Run-time error 13 occurs here"
        End With
    
    End Sub
    Last edited by Proj_Eng; 05-22-2020 at 11:22 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Want to set same print range to many sheets
    By PeteABC123 in forum Excel General
    Replies: 2
    Last Post: 11-14-2019, 11:16 AM
  2. Macro Help to print a range of sheets
    By Philipsfn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2017, 10:39 PM
  3. Print Multiple Sheets from a Listbox on a UF as one Print Job
    By craigos in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2014, 06:46 AM
  4. userform listbox
    By tsiguy96 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-14-2013, 11:09 PM
  5. Printing sheets from listbox as a single print job
    By longrock in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-17-2011, 07:22 AM
  6. Print only selected sheets from listbox
    By stevemcleod in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2010, 03:24 PM
  7. how do you set or reset the print range for several sheets at a ti
    By Mestrella31 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-10-2005, 02:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1