Print Code by specific user

I'm using the following code to automatically print my file via vba. But now I need to run different code depending on the user. How can I make this code switch to a different code when a different user opens the file? The code called "PrintBCJobs" should be run when user "jdoe" opens the file. The code called "PrintBCJobsLS" should be run when user "lsalvucci" opens the file. How can I specify this in VBA?

Sub PrintBCJobs()
Dim ActivePrint As String
    ActivePrint = Application.ActivePrinter
    ActiveSheet.PageSetup.PrintArea = ""
    Application.ActivePrinter = ActivePrint
    ExecuteExcel4Macro _
        "PRINT(1,,,1,,,,,,,,2,""ActivePrint"",,TRUE,,FALSE)"
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveSheet.PageSetup.PrintArea = ""
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWorkbook.Save
End Sub


Sub PrintBCJobsLS()
    ActivePrint = Application.ActivePrinter
    ActiveSheet.PageSetup.PrintArea = ""
    Application.ActivePrinter = "Dell 2150cdn Color Printer on Ne06:"
    ExecuteExcel4Macro _
        "PRINT(1,,,12,,,,,,,,2,""Dell 2150cdn Color Printer on Ne06:"",,TRUE,,FALSE)"
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveSheet.PageSetup.PrintArea = ""
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWorkbook.Save
End Sub

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
armchair_scouseCommented:
Ooops... forgot to add...  once you've worked out who the user is, your code could be modified to include a Case statement, e.g.

Select Case UserID
   Case "Fred"
      ... run some code
   Case "Bert"
     ...  run some different code
End Select
0
 
armchair_scouseCommented:
You can use something like the following (if on Windows) to read the user name from the environment settings:

UserID = Environ("USERNAME")
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
So is this how the code would look? Because it doesn't seem to run at all. Am I missing something?

Sub PrintBCJobsReport()

UserId = Environ("USERNAME")

Select Case UserId
   Case "lsalvucci"
        PrintBCJobsLS
   Case "sconnell"
        PrintBCJobs
End Select

End Sub

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Martin LissOlder than dirtCommented:
Have you dim-ed UserId somewhere?

Are those UserIds correct? You can check your own by clicking in the left-hand margin of line 5 and when the code gets there (if it does), see what's in UserId. If you need more help debugging you can read this article I wrote. It's aimed at VB6 but most of it applies to VBA as well.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
No I did not dim the UserId. What should I DIM it as?
0
 
Martin LissOlder than dirtCommented:
Dim UserID As String
0
 
Martin LissOlder than dirtCommented:
Also if someone else is running the code you can do this and have them tell you what their userid says.

Sub PrintBCJobsReport()
Dim UserID As String

UserId = Environ("USERNAME")
MsgBox "My UserID is " & UserId
Select Case UserId
   Case "lsalvucci"
        PrintBCJobsLS
   Case "sconnell"
        PrintBCJobs
End Select

End Sub
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Working now. Thank you very much!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.