Error 2015

Question

Hi,

I am having trouble with a VBA code that is giving me an error 2015, also I think I have an error when trying to set the correct Printer_Name.

Please note that I'm using "Application.Inputbox" as I like the way it looks.

I have BOLD the sections that I'm having the problems with, that I am aware of.

Also, is it possible to set the width of the Inputbox?

Here is my current code:

Sub Select_Which_Printer_for_PrintOut()

    'Print the Video Tickets

    'System Settings

    Dim Printer_Name0 As String

    Dim Printer_Name1 As String

    Dim Printer_Name2 As String

    Dim Printer_Name3 As String

    Dim Printer_Name4 As String

    Dim Printer_Name5 As String

    Dim Printer_Temp As String

    Dim Printer_Code As Variant

    Printer_Name0 = Application.ActivePrinter

    Printer_Name1 = "Kodak ESP+7 on Ne01:"

    Printer_Name2 = "HP Photosmart B110a Series on Ne02:"

    Printer_Name3 = "Lexmark JetPrinter 2030 on ????:"

    Printer_Name4 = "< Spare > on ????:"

    Printer_Name5 = "< Spare > on ????:"

    Printer_Name6 = "< Spare > on ????:"

    Printer_Name7 = "< Spare > on ????:"

    Printer_Name8 = "< Spare > on ????:"

    Printer_Name9 = "< Spare > on ????:"

    'Question to Set the Printer to Use

Question:

    Printer_Code = Application.InputBox("Please select the Printer you wish to use..." + Chr(10) _        + Chr(10) _        + "     Please enter the Number for the Printer..." + Chr(10) _        + Chr(10) _        + "          1. " + Left(Printer_Name1, Len(Printer_Name1) - 9) + "." + Chr(10) _        + "          2. " + Left(Printer_Name2, Len(Printer_Name2) - 9) + "." + Chr(10) _        + "          3. " + Left(Printer_Name3, Len(Printer_Name3) - 9) + "." + Chr(10) _        + "          4. " + Left(Printer_Name4, Len(Printer_Name4) - 9) + "." + Chr(10) _        + "          5. " + Left(Printer_Name5, Len(Printer_Name5) - 9) + "." + Chr(10) _        + "          6. " + Left(Printer_Name6, Len(Printer_Name6) - 9) + "." + Chr(10) _        + "          7. " + Left(Printer_Name7, Len(Printer_Name7) - 9) + "." + Chr(10) _        + "          8. " + Left(Printer_Name8, Len(Printer_Name8) - 9) + "." + Chr(10) _        + "          9. " + Left(Printer_Name9, Len(Printer_Name9) - 9) + "." + Chr(10) _        + Chr(10) _        + Chr(9) + "The Current Printer is " + Chr(147) + Left(Printer_Name0, Len(Printer_Name0) - 9) _        + Chr(148) + "." + Chr(10) _        + Chr(10), "Print Video Tickets...", 1)

    'Checking User Input

    If Printer_Code = False Then

        GoTo Cancel

    ElseIf Printer_Code = "" Or Not IsNumeric(Printer_Code) Or Printer_Code < 1 Or _        Printer_Code > 9 Then

        GoTo Question

    End If

    'Selecting which Printer to Use

    Printer_Temp = "Printer_Name" + Printer_Code    Application.ActivePrinter = Printer_Temp

    'Print_Out

    'ActiveSheet.PrintOut

    MsgBox (Left(Printer_Temp, Len(Printer_Temp) - 9)) 'Delete as for Testing...

Cancel:

    Application.ActivePrinter = Printer_Name0

End Sub

Thank you for taking the time to look at my problem.

Neil

Answer 1

The channel number is in the "workCol1", I believe that the parts I’ve put inBOLD is where the list is created. Is this where I am to put this new bit of code?    YES

The parts

.Range(workCol1 & LC)

.Range(workCol1 & LC + col2Value)

and

.Range(workCol1 & LC + col3Value)

are 'channelnumber'. 

.Range(workCol1 & LC)

becomes

String(3-Len(Cstr(.Range(workCol1 & LC))),32) & Cstr(.Range(workCol1 & LC))

.Range(workCol1 & LC + col2Value)

becomes

String(3-Len(Cstr(.Range(workCol1 & LC + col2Value))),32) & Cstr(.Range(workCol1 & LC + col2Value))

and

.Range(workCol1 & LC + col3Value)

becomes

String(3-Len(Cstr(.Range(workCol1 & LC + col3Value****))),32) & Cstr(.Range(workCol1 & LC + col3Value))

Answer 2

With regard to the Data Validation issue, I had a similar problem when I had a long, DV comma-delimited list that was formed by a string that was generated "on the fly".  Saving the file as an .xlsm file resulted in that same error.  Saving it as an .xlsb file worked OK.  It seemed to have something to do with the length of the DV string.

So if your original happens to have a long DV string for that cell, perhaps saving it as xlsb might solve the problem.

Answer 3

I just hate it when you do that!  Short and sweet and I think it should more than fill the bill - at least it would for me.  Even the way I was tackling it (from some stuff I put together about 5 years ago) does not afford the opportunity to go into the setup for the selected printer.

EXCELLENT, ELEGANT SOLUTION!

Answer 4

Re:  display list of printers

Depending on your actual need, maybe this will do the job...

'---

'Display list of printers to user

'selecting an item changes active printer - Jim Cone

Sub PrinterSelectionDemo()

 Dim bChoice As Boolean

 bChoice = Application.Dialogs(xlDialogPrinterSetup).Show(ActivePrinter)

 If Not bChoice Then

    MsgBox "User cancelled"

 Else

   MsgBox ActivePrinter

 End If

End Sub

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 5

I have a workbook that demonstrates "enumerating" the printers available to Excel and also shows how to assign one of them as the active printer.  You may be able to take that and adapt it to what you're doing.

It contains a big code module full of API calls, another module that is the "user interface" side of it, plus the one worksheet in it has some Worksheet_Change() event code to actually assign one of the available printers as the active printer.  What it does is use the API calls to find all of the available printers and put them into a cell as their data validation source.  Then you go to the cell (E1) and select from the dropdown list to assign one of them as the active printer.

Now, when I just opened this file a second time this evening in Excel 2010, I got an error saying that it couldn't read all of the file - and I let it "recover" what it could.  What it said it couldn't recover was the Data Validation assigned to cell E1.  But I just clicked the "blue button" on the sheet, and it all seemed to work quite well then.   The specific error message was "Found unreadable content..." and after allowing it to recover, it said the problem was with the data validation part.

In short, clicking the "blue button" gets the list of available printers.  Selecting one of them from cell E1 assigns it as the active printer.

What you'd need to do is copy all of that API code into your workbook and go from there.  I'll tinker here at my end and see if I can't get something that works a bit more like what you've set up above but isn't working yet.

Here is a link to the workbook:

EnumerateAndSelectExcelPrinter.xlsm 61.14KB

[Edit] Link to file removed - turned out to somehow be corrupted & repair removed code! - JLL

And here's a link to an MVP blog entry that has those API calls and some sample code to get the list of available printers and display them.

http://word.mvps.org/faqs/MacrosVBA/AvailablePrinters.htm

[Edit] Actually I'm going to have to work at this.  Several issues at hand:  InputBox doesn't like really long prompts - that's throwing an error on my system (I have some 14 different printer devices, real and virtual).  Also, the Application.ActivePrinter = "printername" also needs the printer port as a second parameter.

QUESTION: If we get the user's choice through a UserForm, are you comfortable with that?  It will allow us to provide the long list of printers that you may need for use.