VB, Input Boxes and Printer Selection

[Odyrus]

Hello,

I have a few bits of code that work well independently. My objective is to put them together into one macro and this is where I'm having trouble.

My goal upon selecting the print button is for the user to input the corresponding number to print that list, be given a choice of printers, and then print. Pretty straight forward I think. I'm having difficulty nesting the steps.

Appreciate any help! Cheers!

This bit of code for Printer Selection works great:

'select printer
    Dim x As Boolean
    x = Application.Dialogs(xlDialogPrinterSetup).Show
    If x = True Then
         
'Print workbook
    ActiveWorkbook.PrintOut Copies:=1, Collate:=True
        
    Else
    x = MsgBox("   Print Cancelled", vbOKOnly, "Cancel Print")
    End
    End If

This bit of code "mostly" works. It will print the team but it's randomly pasting a name into the current worksheet. I haven't figured that out yet.

 Application.ScreenUpdating = False
    
    Dim MyValue As Long, Response As Long
        
        MyValue = Application.InputBox( _
        Prompt:="To Print your team enter the correct number" & Chr(13) & "1 = David, 2 = Jason" & Chr(13) & "3 = Monte, 4 = Natalie" & Chr(13) & "5 = Oscar, 6 = Joe" & Chr(13) & "7 = Erica", _
        Title:="Print Your Team", _
        Type:=1)
          
 
        If (MyValue = 1) Then
        For Each MyCell In Range("David")
             ActiveCell.FormulaR1C1 = MyCell.Formula
             Range("B5").Select
             ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
             Next
             End If
           

        If (MyValue = 2) Then
        For Each MyCell In Range("Jason")
             ActiveCell.FormulaR1C1 = MyCell.Formula
             Range("B5").Select
             ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
             Next
             End If
             
             
        If (MyValue = 3) Then
        For Each MyCell In Range("Monte")
             ActiveCell.FormulaR1C1 = MyCell.Formula
             Range("B5").Select
             ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
             Next
             End If

      
        If (MyValue = 4) Then
        For Each MyCell In Range("Natalie")
             ActiveCell.FormulaR1C1 = MyCell.Formula
             Range("B5").Select
             ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
             Next
             End If

       
        If (MyValue = 5) Then
        For Each MyCell In Range("Oscar")
             ActiveCell.FormulaR1C1 = MyCell.Formula
             Range("B5").Select
             ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
             Next
             End If

    
        If (MyValue = 6) Then
        For Each MyCell In Range("Joe")
             ActiveCell.FormulaR1C1 = MyCell.Formula
             Range("B5").Select
             ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
             Next
             End If

     
        If (MyValue = 7) Then
        For Each MyCell In Range("Erica")
             ActiveCell.FormulaR1C1 = MyCell.Formula
             Range("B5").Select
             ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
             Next
             End If
             
    Application.ScreenUpdating = True

Again, thanks for the extra eyes! Cheers!!

[Bob Phillips]

My guess is that the ranges David, Jason, ..., Erica are single cell ranges, and the code clearly states

 ActiveCell.FormulaR1C1 = MyCell.Formula

which suggests that is where the name in the worksheet is coming from.

Are all those ranges on the same sheet and you want to print just that part, or are they on separate sheets (I cannot see where the sheet gets selected if so)?

[Odyrus]

Hi,

The ranges listed (names of people) are named ranges. They are potentially dynamic so the named range is set to an offset formula that will expand or contract the range accordingly.

Cell B5 is a list validation drop down containing every person on all the teams. The sheet the print button will reside on is an individual report for people; basically select a name from a drop down and it populates a bunch of data.

My goal is to have the user select the print button and input the number corresponding to the named range so that only that specific team prints out rather than going through the entire validation list, selecting individuals from his or her team to print.

Hope this helps.