Printing in Color with VBA

tygrrboi

Well-known Member
Joined
Sep 8, 2015
Messages
1,196
Scenario: I am currently writing an excel document with VBA userforms and macros to populate a sheet with data, print it, and repeat the process a number of times.
The user has already input several people's names into several text boxes all named "txtName1" "txtName2" etc.

Code:
Dim Name As String
Dim numofpeople As Integer           '    ///previously set earlier in the code with no problems (based on counting the number of text boxes that aren't blank)
Dim i As Integer

For i = 1 To numofpeople

Name = Controls("txtName" & i).Value

    Sheets("Form").Select
    Range("J4").Value = Name     '   /// fills in the cell which links to the data on the sheet which will be printed
    
    Select Case cmbTypeofDocument.Value       '  /// combo box the user selects to determine what document to print for all the names listed
        Case "Type A"
            Sheets("AA").Visible = True
            Sheets("AA").Select
        Case "Type B"
            Sheets("BB").Visible = True
            Sheets("BB").Select
        Case "Type C"
            Sheets("CC").Visible = True
            Sheets("CC").Select
        Case "Type D"
            Sheets("DD").Visible = True
            Sheets("DD").Select
        Case Else
            MsgBox "No Document Type Selected.  Please try again."
            Unload userformDataEntry
    End Select

    ActiveSheet.PageSetup.BlackAndWhite = False                  '    /// code I found online while searching
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
Next i

Sheets("Form").Select
    Range("J4").Value = ""            '   /// To reset the name to nothing.



Sheets("AA").Visible = False
Sheets("BB").Visible = False
Sheets("CC").Visible = False
Sheets("DD").Visible = False

MsgBox "Finished!  Please collect your documents from the printer."

Unload Me

Printing works fine on my computer where the printer settings have been set to allow color printing by default.
The issue is that I am sending this document to several other offices where the computers tend to have their default setting as black and white (to conserve ink).

Is there something I can do to either 1) have the VBA code tell the printer to override the default and print in color anyway or 2) Have the default printer's settings window pop up (as would normally do if you select "Print" from the task bar) so the user can double check that the settings allow for color before the program prints the documents

The reason why I entered the code
Code:
ActiveSheet.PageSetup.BlackAndWhite = False
was because it seemed to be relevant, but it turns out it only makes changes to the document and not the printer... so the printers default prints in black and white anyway... basically this code is useless for me.

Many of the forums I read online ask me to type in the printer name... That would be fine for my computer, but as I said, several users are using this and they each have their own default printers. I do not know the names of them.

Is this possible? Please help.
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
The straightforward answer to this is that it's not possible.

The slightly longer answer is that it's hard, working with printers is difficult in VBA, you need to use Windows APIs to call the printer drivers directly and it's messy. It's also not the same for each printer, so it's really probably not worth the effort. Just open the print options box and let them choose colour themselves
 
Upvote 0
Mancubus, not really. It's not an option of either a color printer or a regular printer. It's just one printer that I do not know the name of...


Kyle. I sort of figured it would be a very complicated (almost not worth doing) process. The purpose of this document is to make the process so user friendly that it is practically impossible for the user to fail. Often the person printing the document is barely capable of even filling out an online form... Therefore I wanted to have everything done for them, no matter the amount brainpower and hard work required from me in making it. But I suppose I could trust them to switch the printing to color on their own, provided that I at least give them the opportunity to do so before 20 pages come out of the printer. Therefore....

Is there a way to simply call up the print options box within the coding (before the .printout code) , so they could make the changes themselves? The way I figure it... there must be a way to do it. Clicking "print" in the task bar is the same for everyone no matter what type of printer they have, correct? What is the code to merely open up that window? If you could tell me that, I would be very appreciative.

But just for curiosity's sake, could you also point me in some direction where I would be able to learn about APIs on my own...because Excel actually thrills me to my very being and despite not being a programmer I love spending hours and hours trying and failing until I get it right (as long as I have some sort of guidance). Based on your experience, any recommended reading or viewing on the subject matter?
 
Upvote 0
Sorted out how to open the printer window... after hours of searching and trials.

Code:
Sub PrinterOpen()
Application.Dialogs(xlDialogPrint).Show
End Sub
I ran this code on the first run through the loop, having the user manually change their settings to color with their own printer. then for the following pages I just printed and so far, the printers have remembered the color setting for each additional page.

Code:
For i = 1 To numofpeople

'code to fill out the name on the pages or whatever for each sheet

    If i = 1 Then
        Call Commands.PrinterOpen
    Else
        ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
    End If
Next i

I still would be interested in learning about APIs tho... if anyone has any suggestions for any good resources.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top