Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a Form with Print Options (Excel 2000)

    Hello,

    I am building a form which allows me to pick between Two Printing options
    both options differ by selecting Sheets that will be printed from a list.

    I have the form build and Code filled in but I am running into two problems
    1, I want to select the printer from a list. in E6. Right now I have Two printers but more will be added.
    2. The selection of sheets from the list. Right now I have it selecting manually in the written code. I would like to have the macro figur out which sheets
    to print from the list, in case I decide to delete one from the list.

    Inside the form there is 2 button Print List 1, Print List 2. Each print from different lists.

    If anyone is familiar with a way I can accomplish this I would appreciate the help.

    Thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a Form with Print Options (Excel 2000)

    I think that you want code something like this for Macro1 and similar code, modified to work with list 2 for Macro2:

    <code>
    Sub Macro1()
    'Goes through a list B7:B10 and now knows which sheets will be printed
    'Goes to each of those sheets and makes sure that the autofilter is set to NOBLANKS
    'If Not seet to No blanks this will be done
    'Selects the Printer in cell "E6" on the Options Sheet
    'Prints and returns to the Option sheet

    Dim lMax As Long, I As Long
    Application.ActivePrinter = Sheets("Options").Range("E6").Value
    lMax = Worksheets("Options").Range("B65536").End(xlUp).Ro w - 1
    MsgBox "Printing List1.", vbInformation, "PrintList1"
    For I = 6 To lMax
    With Worksheets(Worksheets("Options").Range("B1").Offse t(I, 0).Value)
    .Range("A1").AutoFilter Field:=1, Criteria1:="<>"
    .PrintOut Copies:=1
    End With
    Next I
    End Sub
    </code>
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a Form with Print Options (Excel 2000)

    Hi Legare,

    Thanks for the help.
    I have a question though,


    lMax = Worksheets("Options").Range("B65536").End(xlUp).Ro w - 1
    can I specify a the last cell in the list and up from there?

    ex
    lMax = Worksheets("Options").Range("B10").End(xlUp).Row - 1

    and changing:
    For I = 6 To lMax
    With Worksheets(Worksheets("Options").Range("B1").Offse t(I, 0).Value)

    I am trying to specify a range to look in for a list of worksheets. so the range here would be B7:B9 for macro1()

    Thanks

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a Form with Print Options (Excel 2000)

    I thought you wanted to be able to change the number of sheets to print by adding to or deleting from the list, that is why I wrote the code to find the last used cell in column B. You could change it to what you coded, or if the list is always B7:B9, just:

    <code>
    lMax = 8
    </code>

    I am not sure what you are asking in the "and changing" part of the question.
    Legare Coleman

Posting Permissions

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