VBA to check if the person using excel is the same as loged in the PC

Dear all,

I'm looking for a VBA macro that will look at the user that is loged in the PC and if that is the one, he can enter information in a cell. This is to avoid that some other person can fill in a cell in a worksheet that is not ment for her/him.
We need to verify the person.

Is this possible? just a VBA macro that looks at the login name from a person in the PC and compares?

Thanks in advance.

Originally Posted by Legend Rubber

so if i had a formula that was in a cell that i didnt want anyone to change how could i do that?

Yes, your code will do that. Note that this formula has the relative reference C50 so would only work for a particular cell. It is possible to use RC addressing to avoid that problem, if there are multiple cells where you want to do this.

Do note that usually it is frowned on to ask a question in another person's thread but this is so closely related to the solution I provided that it makes sense to answer it.

Originally Posted by Megatronixs

Can you please maybe explain shortly how it works?

Not sure how much you already know about macros so I'll start from the beginning.

In Excel, certain actions that a user takes trigger an event. If Excel sees one one of these events occurring, it will look for a Sub with a name matching the event. In this case, the event is the user changing the contents of a cell in a particular worksheet. Worksheet_Change is called when this happens, and Target is the range of cells that was changed.

Intersect is a function that returns the intersection of multiple ranges. If it returns a special value called Nothing, then there is no intersection. So first if the intersection of the updated cell and the cell we are watching is not Nothing (note the double negative here, especially non-native English speakers), then the cell we are concerned about has changed.

Then we are going to use the Environ function to get the Windows username for the person currently logged in. If it matches the person we are looking for, we continue with our code.

Next we disable automatic event handling. That is because we are about to change the value of the cell that the user just changed. If we fail to disable event handling, Excel will try to call this Sub again as soon as we change a cell, and then we change it again, and it gets called again, and we are in an infinite loop. We change the cell back to blank, re-enable events, and give the user a little message that he tried something he's not allowed to do.

To add a second or third person, how to add it to this macro?
can I add a range and specify different cells that are not in a range?

See code below for answer to the first question.

Not sure what you mean by the second question. You can have whatever cells you want to monitor in your Range, but the code has to change if there are more than one. If that's what you mean let me know. The code would have to add a loop that blanked out every cell that the user tried to change. They could do this for more than one cell at a time by doing a paste, for example.

Please Login or Register  to view this content.

Hi,

Thanks a lot for your explanation and your help.
I tried this at work and it does what it needs to do. When I did check it on some one else pc to see if they can change things, well it showed the message and did delete all the information that was on the cell already. Is there a way to prevent that when the cell is filled in, it will not delete it?
I did also protect the sheet with a range so people can't enter nothing just with a macro button that takes your login name and puts it there and all other cells are unlocked. This way the person allowed to add his name can. But if some one else pushes the button it will simply empty the cells (I used a range in column A).

Any help for this is really appriciated ;-)

Originally Posted by Megatronixs

When I did check it on some one else pc to see if they can change things, well it showed the message and did delete all the information that was on the cell already. Is there a way to prevent that when the cell is filled in, it will not delete it?

I mentioned in an earlier post that this is a rather messy problem that I have never dealt with. The Change event will detect that a cell changed, but once it changes there is no automatic way to retrieve the previous value. Running a macro empties the Undo stack. The only way to I can think of to solve this is to have some sort of "shadow" worksheet that is a copy of the one that the user works with. This shadow sheet would be hidden. Every time a change is made on the visible worksheet, VBA code would check to see if the change is authorized. If it is authorized, it would allow the change and copy the change to the shadow worksheet. If the person is not authorized, it would restore the value previously saved on the shadow worksheet.

There could certainly be an easier way to do this that I am not aware of. To provide a solution I would have to have your actual file, and frankly I'm not sure if I could find the time to do it.

Hi,

Thanks a lot for your help. I think I can find a solution for this. I just protect the workbook and a macro that will be run by pressing a button will put the name of the user in the cell of the protected range and if you are not authorized, then you will get a message and can't make changes to the cell. I will comment out the part that deletes the cell content. I guess that this will do the trick.
Once I have a working macro I will share this with you and others so one can make use of it as I think is a great solution to have some kind of protection in a sheet who can enter something.

Once again BIG THANK YOU.

Greetings,
megatronixs

Originally Posted by Megatronixs

I just protect the workbook and a macro that will be run by pressing a button will put the name of the user in the cell of the protected range and if you are not authorized, then you will get a message and can't make changes to the cell. I will comment out the part that deletes the cell content.

I am not quite sure what you mean but doing this through protection is a good idea and much better than the convoluted solution I just described. You can put this macro in the module ThisWorkbook, which will unlock the desired cell if the user is authorized, and lock it otherwise. If you protect the worksheet, you may want to include a password.

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

Do note that usually it is frowned on to ask a question in another person's thread but this is so closely related to the solution I provided that it makes sense to answer it.

Sorry, i did hesitate and wonder about PM'n you, but decided that it may have relevence to Megatronix's question anyways...

Hi,

It is good, but I found something else that needs to be done. Is it possible that once a cell is filled in with the login from a person, that it can't be changed later. So they can edit once the cell. THis will be much more save as one will not be able to over write the previoius cell's content.

What is the value in the cell before it is edited? Is it always the same so that you can tell whether the original value has been changed?

Hi,
Initially the cell is empty, and after the person putting his log in from the pc, the cell should be not able to be modified. So the value (once filled in) should be the same.
Do you think it is possible to not allow changes to cells that have content?

Greetings,
megatronixs

This modification will allow the user to change the cell only if it's blank.

Please Login or Register  to view this content.

Hi,
I tried out and worked, but when I share the workbook I got error 1004. So this was keeping me going mad to find out what happened. I guess I need to solve it in a different way as it needs to be a shared workbook.
What I don't understand is how the password works. You have the comment ' add password here if using one.
Pleases let me know where or how to put it.

Thanks a lot.

Suppose your password is AbCdEfG. See changes in red to apply password.

Please Login or Register  to view this content.

Hi,
Thanks. Does this work also on excel 2003? as I can see you have office version 2010.

greetings

Yes, this is the same in 2003, 2007, 2010.

I haven't done much work with shared workbooks but I know there are some restrictions on what you can do with them. I will look into that if I get some time today.