print to default printer so I don't have to open notepad

Hi everybody,

I have a workbook that has a Textbox and also creates a standard txt file from the info in the textbox. In my code, is there a way to have it print to my default printer when I click on the command button so I don't have to open notepad and then print.

Thanks in advance,
Myke

Hello GuitarMyke,

This macro will print a file to the default printer without opening the program first. If you want to open the original program, change the constant vbHide (in blue) to vbNormalFocus. If you don't include the file path, the current directory will be used.

'Written: March 15, 2008
'Author:  Leith Ross
'Summary: Prints a file using the default printer


'This will print a file if it supports the Print Command
 Private Declare Function ShellExecute _
  Lib "Shell32.dll" _
    Alias "ShellExecuteA" _
     (ByVal hWnd As Long, _
      ByVal lpOperation As String, _
      ByVal lpFile As String, _
      ByVal lpParameters As String, _
      ByVal lpDirectory As String, _
      ByVal nShowCmd As Long) As Long

Sub PrintFile(ByVal FileName As String, Optional ByVal FileDir As String)

  Dim Ret As Long
  
    If FileDir = "" Then FileDir = CurDir
    
    Ret = ShellExecute(0&, "print", FileName, vbNullString, FileDir, vbHide)
    
   'Did Connection Fail? Errors are from 0 to 32
    If Ret <= 32 Then
       Select Case Ret
         Case 2     'SE_ERR_FNF
           Msg = "File not found"
         Case 3      'SE_ERR_PNF
           Msg = "Path not found"
         Case 5      'SE_ERR_ACCESSDENIED
           Msg = "Access denied"
         Case 8      'SE_ERR_OOM
           Msg = "Out of memory"
         Case 32     'SE_ERR_DLLNOTFOUND
           Msg = "DLL not found"
         Case 26     'SE_ERR_SHARE
           Msg = "A sharing violation occurred"
         Case 27     'SE_ERR_ASSOCINCOMPLETE
           Msg = "Incomplete or invalid file association"
         Case 28     'SE_ERR_DDETIMEOUT
           Msg = "DDE Time out"
         Case 29     'SE_ERR_DDEFAIL
           Msg = "DDE transaction failed"
         Case 30     'SE_ERR_DDEBUSY
           Msg = "DDE busy"
         Case 31     'SE_ERR_NOASSOC
           Msg = "Default Email not configured"
         Case 11     'ERROR_BAD_FORMAT
           Msg = "Invalid EXE file or error in EXE image"
         Case Else
           Msg = " Unknown error"
       End Select
       
      Msg = "Unable to Print File " _
          & vbCrLf & "Error Number " & CStr(Ret) & " -  " & Msg _
          & vbCrLf & FileName & vbCrLf _
          & FileDir
        Ret = MsgBox(Msg, vbExclamation + vbOKOnly)
      Exit Sub
    End If
    
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel

Sincerely,
Leith Ross

Thanks Leith,

Macro works great.

Myke