Old Value Cell Reference

%3CLINGO-SUB%20id%3D%22lingo-sub-2747673%22%20slang%3D%22en-US%22%3EOld%20Value%20Cell%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2747673%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20I%20have%20built%20that%20I%20am%20so%20close%20to%20finishing.%20The%20issue%20I%20am%20having%20is%20the%20workbook%20has%20a%20login%20user%20form%2C%20the%20admin%20sheet%20logs%20who%20is%20logged%20on%2C%20and%20when%20I%20have%20a%20cell%20that%20shows%20who%20the%20current%20user%20is.%20In%20another%20sheet%20there%20is%20a%20dynamic%20table%2C%20there%20are%20item%20codes%20that%20the%20employees%20enter%20and%20it%20will%20reference%20the%20item%20and%20time%20stamp%20the%20item%20to%20show%20when%20it%20was%20changed.%20I%20need%20to%20reference%20the%20cell%20that%20shows%20the%20current%20user%20BUT%20not%20change%20the%20cell%20of%20each%20row%20when%20a%20new%20user%20is%20logged%20in.%20Please%20help%2C%20I%20have%20researched%20this%20for%20several%20hours%20with%20no%20luck%20with%20my%20particular%20application.%20Images%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2747673%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2752652%22%20slang%3D%22en-US%22%3ERe%3A%20Old%20Value%20Cell%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752652%22%20slang%3D%22en-US%22%3Eso%20tagged%20this%20with%20macros%20and%20VBA%20and%20I'm%20thinking%20you%20are%20using%20VBA%20to%20do%20the%20timestamp.%20What%20I%20don't%20know%20is%20why%20you%20can't%20use%20VBA%20to%20copy%20the%20value%20of%20the%20user%20name%20also.%20Maybe%20you%20could%20attach%20the%20actual%20files%20instead%20pictures%20(remove%20any%20confidential%2Fpersonal%20info).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2763885%22%20slang%3D%22en-US%22%3ERe%3A%20Old%20Value%20Cell%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2763885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached.%20As%20far%20as%20time%20stamps%20go%2C%20no%20I%20was%20just%20using%20a%20formula%20in%20the%20cell%20duplicated%20using%20the%20table.%20the%20ID%20to%20get%20in%20is%201234%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20some%20things%20that%20I%20have%20been%20working%20on%20adding%20and%20have%20run%20into%20snags%20as%20well%20but%20those%20are%20to%20come.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20assistance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2770716%22%20slang%3D%22en-US%22%3ERe%3A%20Old%20Value%20Cell%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2770716%22%20slang%3D%22en-US%22%3EI'm%20having%20many%20problems%20with%20your%20workbook%20not%20opening%2Fclosing%20and%20many%20non-existent%20name%20references.%20I%20don't%20know%20if%20maybe%20you%20deleted%20confidential%20info%20and%20that%20is%20the%20problem%3F%20It%20is%20very%20hard%20to%20tell%20what%20is%20wrong%20in%20the%20mix.%3CBR%20%2F%3EThat%20said%20it%20is%20obvious%20that%20you%20have%20a%20login%20form%20so%20get%20rid%20of%20the%20circular%20references%20and%20have%20the%20macro%20set%20those%20cells%20with%20the%20login%20name%20and%20timestamp.%20Then%20other%20cells%20that%20need%20to%20point%20to%20the%20person%20that%20entered%20the%20data%20have%20the%20macro%20either%20set%20those%20values%20or%20point%20to%20the%20corresponding%20point%20in%20the%20login%20in%20log.%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a workbook I have built that I am so close to finishing. The issue I am having is the workbook has a login user form, the admin sheet logs who is logged on, and when I have a cell that shows who the current user is. In another sheet there is a dynamic table, there are item codes that the employees enter and it will reference the item and time stamp the item to show when it was changed. I need to reference the cell that shows the current user BUT not change the cell of each row when a new user is logged in. Please help, I have researched this for several hours with no luck with my particular application. Images attached

 

7 Replies
so tagged this with macros and VBA and I'm thinking you are using VBA to do the timestamp. What I don't know is why you can't use VBA to copy the value of the user name also. Maybe you could attach the actual files instead pictures (remove any confidential/personal info).

@mtarler 

Please see attached. As far as time stamps go, no I was just using a formula in the cell duplicated using the table. the ID to get in is 1234

 

There are some things that I have been working on adding and have run into snags as well but those are to come.

 

Thanks for the assistance!

I'm having many problems with your workbook not opening/closing and many non-existent name references. I don't know if maybe you deleted confidential info and that is the problem? It is very hard to tell what is wrong in the mix.
That said it is obvious that you have a login form so get rid of the circular references and have the macro set those cells with the login name and timestamp. Then other cells that need to point to the person that entered the data have the macro either set those values or point to the corresponding point in the login in log.

@mtarler 

I am new to VBA and know only what I can find online. I fixed the time stamp in all areas except the admin pages as I can not get that to work. But the circular references are turned off and the error is gone. Perhaps you can help me now. the pas word is 1234

I appreciate it. 

@jdavis1994  i made a few tweaks. 

I changed the worksheet functions from SelectionChange to Change so that it will create the timestamp and user name only when the value is entered/changed

I commented some items out that caused issue

I added the user name entries

I changed the action when you 'x' out of the login window to give option to close the workbook

Hope it is working how you hoped.

FWIW I really hope you don't consider this password protected in any way or even any claim that users can't see each other's information.  There are ways of making it 'better' but in general I would never consider an excel file to be secure.

 

@mtarler 

This s was vey helpful! The security is more or less just to track who is in and out of the sheet. As I know they can get into VBA and disable the log in. The people I am using this for are not this savvy in VBA. So no real issues there.  This was a problem when I sent it to you why does it show the user twice in the log?

@jdavis1994  honestly I'm not too fond of this code but I'm not one to judge.  That said I re-did some of it (again).  I would probably re-do more if I knew exactly what you needed and that I wouldn't be un-doing some functionality you intended.  Tell me what you need and what exactly this is not doing right.