2010 VBA code to select different printer

[lopaka]

We just changed from from Office 2003 to Office 2010 and there is a VBA macro that we use that calls up a ballon with selections of the different printers that can be used . this function no longer works. Below is the code. Can anyone tell me if there is new code that will do the same?
When running the code in debug it stops on the
.Heading = "Printer Selection"


'************************************
'* Choosing the desired printer. *
'************************************
Set b = Assistant.NewBalloon
With b
.Heading = "Printer Selection"
.Text = "Please select a printer."
.Labels(1).Text = "MTBE Control Room CXO086"
.Labels(2).Text = "DeepWell Printer CXO115"
.Labels(3).Text = "Nancy's Printer CXO077"
.Labels(4).Text = "Nancy's Copier/Printer CXO072"
.Labels(5).Text = "User Selectable Printer"

ReturnValue = .Show

End With
'MsgBox ("ReturnValue:") & ReturnValue
Select Case ReturnValue
Case 1 '"MTBE Control Room Printer"

Desired_Printer = "\\CXOFS01\CXO086"
'MsgBox ("Desired_Printer:") & Desired_Printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=Desired_Printer, Collate:=True

Case 2 '"DeepWell Printer"

Desired_Printer = "\\CXOFS01\CXO115"
'MsgBox ("Desired_Printer:") & Desired_Printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=Desired_Printer, Collate:=True

Case 3 '"Nancy's Printer CXO077"

Desired_Printer = "\\cxofs02\CXO077"
'MsgBox ("Desired_Printer:") & Desired_Printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=Desired_Printer, Collate:=True

Case 4 '"Nancy's Copier/Printer CXO072"

Desired_Printer = "\\cxofs01\CXO072"
'MsgBox ("Desired_Printer:") & Desired_Printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=Desired_Printer, Collate:=True

Case 5 '"Default"

'Desired_Printer = Application.ActivePrinter
'MsgBox ("Desired_Printer:") & Desired_Printer
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.SendKeys ("^(p)")

End Select
'
thanks for any help anyone can give

 

[Trevor G]

We just changed from from Office 2003 to Office 2010 and there is a VBA macro that we use that calls up a ballon with selections of the different printers that can be used . this function no longer works. Below is the code. Can anyone tell me if there is new code that will do the same?
When running the code in debug it stops on the
.Heading = "Printer Selection"


'************************************
'* Choosing the desired printer. *
'************************************
Set b = Assistant.NewBalloon
With b
.Heading = "Printer Selection"
.Text = "Please select a printer."
.Labels(1).Text = "MTBE Control Room CXO086"
.Labels(2).Text = "DeepWell Printer CXO115"
.Labels(3).Text = "Nancy's Printer CXO077"
.Labels(4).Text = "Nancy's Copier/Printer CXO072"
.Labels(5).Text = "User Selectable Printer"

ReturnValue = .Show

End With
'MsgBox ("ReturnValue:") & ReturnValue
Select Case ReturnValue
Case 1 '"MTBE Control Room Printer"

Desired_Printer = "\\CXOFS01\CXO086"
'MsgBox ("Desired_Printer:") & Desired_Printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=Desired_Printer, Collate:=True

Case 2 '"DeepWell Printer"

Desired_Printer = "\\CXOFS01\CXO115"
'MsgBox ("Desired_Printer:") & Desired_Printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=Desired_Printer, Collate:=True

Case 3 '"Nancy's Printer CXO077"

Desired_Printer = "\\cxofs02\CXO077"
'MsgBox ("Desired_Printer:") & Desired_Printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=Desired_Printer, Collate:=True

Case 4 '"Nancy's Copier/Printer CXO072"

Desired_Printer = "\\cxofs01\CXO072"
'MsgBox ("Desired_Printer:") & Desired_Printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=Desired_Printer, Collate:=True

Case 5 '"Default"

'Desired_Printer = Application.ActivePrinter
'MsgBox ("Desired_Printer:") & Desired_Printer
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.SendKeys ("^(p)")

End Select
'
thanks for any help anyone can give

Welcome to the Forum,

The Office assistant (New Ballon) was removed in Office 2007 so your code wont work as it is. The thing you want to do is create a UserForm and work with either Tick Boxes or a combo box then your case statement will work.

 

[lopaka]

I know this will sound dumb.......but how do you do that?? I have never used userforms or tick boxes

 

[Trevor G]

You would need to use the Alt + F11 key to get into the VBA screen and then select the Insert Menu and then UserForm.

Once you add the form you will invoke the ToolBox. I would suggest using the Checkbox command and a Command Button.

You would look at the properties to each item and then name each checkbox individually and also the Command Button.

Checkbox names should be chk and then a name no spaces.

Command Button name cmd and then a name no spaces.

Then you add the code behind the command button as per your case statement and refer to the checkbox names if checked to print your sheets.

you then need to be able to invoke the form so you would add a module sheet and then add the code to invoke the form.

Sub OpenForm1()
UserForm1.Show
End Sub

You can then either run the macro from a button on the sheet or through the Run Macro command (Alt + F8)

You can always search the net on how to create userforms. Possibly watch something on YouTube to help you. Or purchase one of the products from MrExcel store.

I hope that helps you out.