Insert user's name into cell based on windows user login when a button is clicked.

Hi jonvanwyn,

Below is the code to enter a date stamp into a cell and a username into a different cell. Let me know if you can adapt this to meet your needs. If you need more specific help with your VBA, can you possibly post a copy of your Excel Workbook in .xls format so that I can take a look.

Please Login or Register  to view this content.

Understood. I wasn't sure if you simply needed the syntax for these procedures or if you needed the full procedure. Hence, I included this statment: "Below is the code to enter a date stamp into a cell and a username into a different cell. Let me know if you can adapt this to meet your needs. If you need more specific help with your VBA, can you possibly post a copy of your Excel Workbook in .xls format so that I can take a look." A copy of your workbook can help with testing the code we are developing without having to recreate our vision of what we think the workbook should look like. It can also help us determine if there are tenable workarounds in situations where the code may be a bit tedious.

hey jonvanwyk

I found your macro very useful indeed thanks!!

Hi Jon,

Glad I caught this thread. First, I will include code that will hopefully meet your needs with regards to row 20:

Please Login or Register  to view this content.

Next, I will make a few comments about the spreadsheet. First, it looks great and it's very well put together. Unfortunately, due to certain limitations in Excel (unless changes have been made in newer versions), the command buttons do not fully interface with the spreadsheet. To be more specific, simply because the "Request" button sits in row 20, excel doesn't recognize it as a button on row 20. With that said, there's not true method of creating one macro that would work with each button. You'd have to create one macro per button to account for each row. Of course, that is both time consuming and will result in a bloated spreadsheet.

You can use a worksheet change event to trigger the macro, then you can capture the row that is being queried. For example, if a user double clicks on the actual date (ie, cell A20), a macro can be fired that acounts for the PTO

Thanks guys...I will see if I can get it to work for me! :-)

snb: If you see my original post, there are also conditions that need to be met based on cells that are blank or not. Also:

- do not use merged cells in combination with VBA
For aesthetic reasons I need to used merged cells with VBA.

- not every macro needs a separate module (put the into 1)
This would be great if I knew how...your help is appreciated

- avoid using 'select' and 'activate' in VBA
I will do my best, but it seems many suggestions I get (that work) do this.

- macro's that only apply to a certain worksheet can be put into the sheet's macromodule
I realize this, but most of my macros serve duty on multiple worksheets.

- you better not use VBA to format cells, if that can be done beforehand.
I usually don't format cells with VBA. I think I did in this case because the format I wanted was not available as an option otherwise?

@bigbas

My plan was to create a macro that just worked for row 20, and then duplicate it into a unique macro for each line after that. Right now I am just trying to get a "template macro" that works and can be modified.

@everyone:

I will have multiple users that will be clicking on these "request" and "volunteer" buttons. The form needs to recognize who is using it and just insert their name in the first available box and then time stamp it.

Jon,

If that's the case, try the code that I provided above for line 20. Also, I noticed that you wanted to convert the "username" into a full name. Right now, this will only work for your name. If you have a list of everyone's username and full name, you can include them on a separate worksheet (hide it if necessary), and have the macro search for the username (ie, jwb) and return the full name (ie, Jon Van Wyk).

Post back with any questions or problems you have adapting the code.

BigBas

You recognized my next question before I asked it. Can't I do something like this? ...

Please Login or Register  to view this content.

Jon,

That would also work, especially if there are only a couple of people who use the spreadsheet and they don't change very often. If you have many users, or there is a high turnaround in users, it can become annoying and inefficient to have to code an if...than statement for each possible user name. In that case, a lookup into a table may be easier. In the case of three users, perhaps another SELECT CASE statement can tidy the code:

Please Login or Register  to view this content.

There are 10 users and they change rarely.

The following is what I came up with combining both suggestions. Based on my windows user login of "jmv", it enters Melanie Waters into D20, and then time stamps M20 instead of G20.

Please Login or Register  to view this content.

I think my brain is incapable of keeping it simple. I should note that BigBas's suggestion functions exactly as I want it to, except I could not get it to work when incorporating multiple users. BigBas, can you show how you would use your Case Select code in the overall scheme?

snb...your kung fu is too strong for me. I find that my modified version of BigBas's code below meets my needs.

Please Login or Register  to view this content.

Now...can I turn said macro "on" and "off" at will? For instance, if users can only request time off during a certain time period, I need to be able to enable and disable just the REQUEST macro. Thoughts?

Update: I will start a new thread for this question.