VBA and Macros / Autopopulating Date Stamp and username

Greetings All. I need help on an excel project I am working on. I have have no experience in VBA and macros with excel. However, I am a whiz at other things in excel which mainly consist of formulas, pivot charts and tables, and statistical analysis. The current project I am working on is gathering data from multiple people. Granted, using the sharing and track changes feature would probably be the best way to go about doing this. However, the people I am gather data from are inept when it comes to excel. I do not what to have a meeting just to go over how to input data into the spreadsheet, and how to create a audit trail of their changes and additions. Hence why I am diving down the rabbit hole into VBA and Macros. I want to make this workbook and intuitive as possible. It is much easier to tell people to enable macros and have things just happen. LOL

So this is what I have done and figured out so far. I have figured out how to change the color of the font if someone makes a change to a cell. I have also figured out how to add a date stamp if someone changes a cell or multiple cells in a row. However, I learned this by watching a youtube video. The instructor only had two columns he was working with: Column A (Name) and Column B (Date Updated). When he updated column A, a date stamp populated the corresponding cell in column B. I have 17 columns in my spreadsheet. I also have 433 rows/record, all needing updates and additional information added. So I have the same code written 17 times. So I am hoping someone can tell me how to consolidate the length of the code yet achieve the same results. Also, I am wondering how to get the person's username who is reviewing, editing, and adding information to the spreadsheet to auto populate in Column T. The date stamp is currently auto populated in Column S. Can a line be added to the code to accomplish this? The code I have is below.

Cheers,
Kaytie

************************************

Please Login or Register  to view this content.

Welcome to the forum.

Please take a few minutes to read the forum rules, amend your thread title accordingly, and edit your post to add CODE tags.

Thanks.

Hi and welcome.

Firstly the moderators are probably going to chase you up over the title and not having your code tagged.

That aside this is totally untested but have you tried

Please Login or Register  to view this content.

I'd use this slight tweak on Crooza's code:

Please Login or Register  to view this content.

HTH

Robert

I did this once but I don't have the file handy at present so again this is totally untested but have you simply tried

Please Login or Register  to view this content.

Hi Kaytie,

You need to use either the Application.UserName or the Environ("username") methods. There is a good thread describing the differences between the two here.

I have used the later function below:

Please Login or Register  to view this content.

HTH

Robert

So if my organization uses employee ID numbers to log on and I use Environ("username"), then Row,20 so populate with the employee ID number?

Correct.

But if I use Application.UserName, Row 20 will populate with the name the employee entered the first time they opened the excel application?

No, it's the default owner name of Excel - where I work it's my company's name. This can be viewed and changed via the four steps listed here. As it can easily changed (though I doubt many people do) it's not as realiable as using the ENVIRON function but you will need a list of employee ID numbers and their corresponding names for that to be meaningful

HTH

Robert

Wow! This is so awesome! Thanks Robert aka Trebor76 and Crooza. This is exactly what I needed. I just would like one last tweek to the code. I do want the font to change color when a change is made. However, I do not need the font to change color in column S (date stamp), column T (reviewer username), and column U (comments). I just want the font to remain black. Can I get a code fix for that?

Please Login or Register  to view this content.

And Robert aka Trebor76 => Employee ID in my organization is very meaningful and very easy to look up. That is exactly what I need in my spreadsheet. Thanks a million!

~Kaytie

Hi Kaytie,

I'm not too sure what you mean - is it that you the font colour for the entire tab in magenta and just columns S, T and U in black?

Robert

akaytie

the code you're using above seems to do exactly what you're requesting and only change the font of the changed cell. I'm not clear what change you're looking for

Ok ignore my comment I posted at the same time as you! So you want the whole row to change?

Hi Kaytie,

Here's one way:

Please Login or Register  to view this content.

HTH

Robert

OK.

WIth your code above I couldn't replicate the issue you have with column S and T changing colour.

Try this it will change the colour from B to R

Please Login or Register  to view this content.

I don't know what I am doing wrong. I think I am putting the code in the wrong place. I clicked on sheet1 and the code window came up. I made sure I selected worksheet the first drop down field. And the second drop down box has the words "selectionchange". I pasted your code into the window. Now every time I click on a cell, move the cursor to the next cell, hit enter, the entire row text changes to magenta from Column A to Q whether or not I actually change the data in the cells. Not what I had in mind. LOL Can you tell me what I am doing wrong? I tried to upload a screen shoot of my VBA editor with no success.

Kaytie

Can you upload your actual worksheet? go to advanced and link it as a file. Remove sensitive data if you need to. If you can't I'll send you my version which I just filled with 'x' from Row A to Row R to test

Maybe this will help. The reason I couldn't attach the screen shot was it was too big.
~Kaytie

OK I tried your spreadsheet.

It seems to be doing what you asked for. That is its inserting the date and time and username to column S and T and then changing the columns B to R to magenta text but leaving columns A, S and T as black.

Are you saying

1. That this isn't working on your machine OR
2. this is working on your machine but it's not the outcome you wanted?

OK. I understand now. The code runs everytime you move out of the cell (regardless of whether there is a change). You want to be able to move around without the cells changing.

Replace your old code with this instead

Please Login or Register  to view this content.

Great. Glad you worked it out. Well done.