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.