Solved

Print Code by specific user

Posted on 2013-01-09
8
171 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now