automatically check who's logged in with VBA?

Hi Folks,

Short summary of my problem: Is there a way to have a macro check who is logged in on a computer and store that information?

Explanation why I need this:

For our main customer, we send invoices once a month and currently, we use a macro which copies the relevant information to a new spreadsheet, sorts it by department (because each department has to pay it's own bills...what a surprise) and automatically saves that spreadsheet as mm-yyyy.xlsx (e.g. 09-2015.xlsx).
However, our secretary then still has to copy the data per department to a Word file, which she then sends to our financial dept., who turn it into an official invoice. Since we usually have quite a large number of invoices per month, making the Word files can sometimes take half a day or more and that's a bit of a waste of time.

We now, finally, have 'permission' from our financial dept. to send pdf's instead of Word files and of course, making a pdf from Excel can easily be incorporated in the macro.
But here comes the problem: We work with a very strict quality control system and need some way to verify who did what. I was first thinking about making some kind of popup window in the macro in which the person running the macro needs to fill in his/her initials before the macro continues. And to have the macro include those initials in the name of the excelsheet when it's saved.

But as our QA manager pointed out: I can just as easily fill in somebody else's initials. Of course, we expect people not to do that, but the board of accreditation will probably see that differently (they usually do).

So what we were thinking now is to have the macro check who is logged in on the computer on which the macro is run and include those data in the spreadsheet, either in a set place on one of the sheets or, preferably, in the file-name.
And of course, we know that it is still possible to run the macro on a computer on which somebody else is logged in but hey....we can't cover everything.

Hope you can help me.

Cheers,
Thirsa

This function:

Please Login or Register  to view this content.

Will return the windows logon of the person who is running the macro (well logged into windows at the time anyway)

Cool, I'll see if I can incorporate this into my macro.
Thanks for the quick reply!
Thirsa

And less reliable. Is there a reason for using it Richard? I tend to avoid it

Hi Richard,

I don't know if your solution will work for us, since as far as I know, when software is installed, the name of the hospital we are part of is recorded instead of individual usernames. But I'm not completely certain of this. I'll simply try both options and see which works best.

Cheers,
Thirsa

Originally Posted by Richard Buttrey

It would seem a shame not to use the Excel personal UserName variable when installing Excel since that is presumably what it's designed for.

I coudn't agree more, but unfortunatly the folks in our ICT dept. and I don't always see eye to eye.
But like I said, I'm not completely certain about what name is recorde when software is installed, so I'll simply try which solution works best.

@Richard, not quite. Where you have multiple users on one machine, you can only insert one username for office.

That's why I asked what the reason for is in using it. I don't understand why it's so widely adopted when it's so frequently incorrect - I'd always go for Environ

@Richard: Your interpretation is correct. It is indeed so that most computers here are used by only one person and I expect the invoice macro to be run only from such computers (usually the secretary's computer and if she's not in either mine or the computer of the head of our dept.).

I've decided to go with for Environ, just to be certain (no offence Richard) and my spreadsheet is now saved as mm-yyyy_username.xmlx.
Thanks again guys!

Thirsa