Solved

Print Code by specific user

Posted on 2013-01-09
8
173 Views
Last Modified: 2013-01-09
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

0
Comment
Question by:Lawrence Salvucci
  • 3
  • 3
  • 2
8 Comments
 
LVL 9

Expert Comment

by:armchair_scouse
ID: 38758821
You can use something like the following (if on Windows) to read the user name from the environment settings:

UserID = Environ("USERNAME")
0
 
LVL 9

Accepted Solution

by:
armchair_scouse earned 500 total points
ID: 38758831
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 38758864
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 46

Expert Comment

by:Martin Liss
ID: 38759200
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 38759286
No I did not dim the UserId. What should I DIM it as?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38759295
Dim UserID As String
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38759307
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 38759308
Working now. Thank you very much!!
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question