VB, Input Boxes and Printer Selection

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:

Code:

---

'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.

Code:

---

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!!

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

Code:

---

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)?

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.