Creating a userlog to go with workbooks

So I need a simple user log to go with my workbook. I have found two how to videos on youtube that are instructive but they are a bit too complicated I think for what I need. So I am hoping someone here can help. For those of you wondering what videos I found they are:

Excel Userform Login - MultiLevel User Login by Trevor Easton

and

Attendance Login Logout UserForm using Excel VBA by Dinesh Kumar Takyar

The problem with the Trevor video is that I don't need to validate the user. I just want to the user to input their name and their employee ID and have excel date and time stamp the action placing the information on Sheet5. The problem with the Dinesh video is, again, I don't need to validate a user and I don't want to have the code referring to another external workbook.

So with all this said, I want the workbook to operate as follows. When a user opens the spreadsheet, I want all sheets hidden except for Sheet1 which will contain instructions how to conduct the review and associated definitions to the worksheet. I want a login button on this sheet. When a user click on this button to begin work, a userform appears prompting the user for their full name and employee ID. I will have a message under these field that says something to the effect "I understand the instructions. I am ready to begin work". When the user clicks on the Start button, the user's full name and employee ID with a date and time stamp is automatically populated on Sheet5. Furthermore I want to unhide Sheets 3 & 4 with Sheet 4 & 5 remaining hidden. However, when the user "admin" with employee ID "u0000000" effectively logs in, I want to unhide all sheets. The above ideas came from the Trevor video.

Lastly when the user goes to exit the workbook, I want a userform to reappear prompting them to log off. I got this idea from the Dinesh video. When a users ends the review aka logs off, I want to date and time stamping the action on Sheet5 and automatically save the sheet to the last known save location.

FYI... uNID is our employee id acronym. This will need some data validation. A uNID starts with the letter u and then has 7 digits.

I have attached a mockup of this workbook. Any help with the VBA coding would be greatly appreciated!

Cheers,
Kaytie

Book2.xlsm

Naveed -

If you do, that would be awesome! I will not be able to thank you enough!

Kaytie

Is it tomorrow yet?
~Kaytie

Hi Kaytie


Is far from complete.
But it seems something
Contact me if you have anything you ask for more help.
I'll help you gladly

Use the "Sub test__beginEndInReview_EventLog()" testing

If the suggestion helps you, then Click * to Add Reputation . Thanks.

Pan123, I am sorry but I couldn't figure it out other than to run the macro to display all my sheets. I am so completely out of my depth here. LOL

With that said, it is the end of the work day and I have made some small progress on my userlog workbook coding project. Unfortunately I do not have access to the building nor the computer I work on during the weekend. And as I run an apple computer at home, I am not gonna touch this project until Monday. So if you can help me make some leaps and bounds by Monday, I'll will be eternally grateful. LOL

So I figured out how to code the command button to show my userform. I also figured out how to hide labels and command button I don't want the user to see on the userform until they attempt to close the workbook. So when a user clicks on the "Click here to display sheets in this workbook." button on Sheet1 (Introduction), the userform appears prompting the user to enter his first name, last name, and university ID number (uNID). I even managed to figure out how to only allow so many characters in the uNID field.

I have yet to figure out how to take all this information and place it on Sheet5 when the user clicks the START REVIEW button. When the user clicks the START REVIEW button, I want the First Name to go to Sheet5 Column B, Last Name to go to Sheet5 Column C, and the uNID to go to Sheet5 Column D. I also need Sheet5 ColumnE (Start) to auto populate the with a date and time stamp. Lastly Sheets 1 through 3 should appear. Ideally if a user inputs "admin" (which will be me or whoever inherits this workbook if I leave) in both the First Name and Last Name fields and "u0000000" into the uNID field, I would START REVIEW button to work like any other user however ALL sheets contained in workbook are displayed and any workbook protections are disabled.

In regards to when a user closes the excel workbook, I have figured out how to display the userform again. The userform displays the following labels and field: First Name, Last Name, Label 5, Label 6, cancel button, end review button, and save review button. The start button and Label 4 are hidden from the user. I have yet to figure out how to code the command button properly. This is how I want each command button to function:

CANCEL button - When the user clicks on this button, I want the userform to close and take the user back to the sheet they were working on. I attempted the code Unload Me but that just closed the entire workbook.

END REVIEW button - When the user clicks on this button, I want the date and time the user ended their review to be auto populated on Sheet2 ColumnF (End). Then as a fail safe, I want the workbook saved at the last known location with overwriting the file. So if the user saved it in C:\Users\u0000000\Documents with the file name Book2.xlsm, then that is how the workbook should be saved. Then the workbook should close. Having said that, if it is the admin closing the file, I want the file to be automatically protected.

SAVE REVIEW button - When the user clicks on this button, I want to have the user save the workbook where ever she chooses to save it. Again if it is the admin, I want the file to also automatically be protected. Then I want the message box to appear, reminding the directing the user to click the END REVIEW button.

So if all you excel jedi masters can you work your voodoo magic that you do and finish this off for me? LOL So that I can use this template workbook and code with my other workbooks that I need to send out to a gazillion users to provide the information I need to do my job.

I am attaching the file. I hope and pray that someone (anyone) will take pity on me and help!

Thanks,
Kaytie

Naveed -
I'm getting an error message when I open the file in Excel 2013. I sent you a private message regarding the particulars. I hope to hear from you soon.

Cheers,
Kaytie

So all you jedi knight VBA gods, the excel spreadsheet that Naveed coded for me works spectacularly well after some formatting and message changes from me. However, in doing these changes I realized I needed two more command buttons on the userform. Naveed, thank goodness, has been very responsive. However, I am running up against a deadline and he is has been suddenly overwhelmed at work. So I managed to code the SAVE button on my own. I just need to code the CANCEL button on the userform.
The intent of the cancel button is to close out the userform and allow the user to keep working in the spreadsheet instead of just closing out excel altogether of which the code "Unload Me" or "unload userform1" does now.
And lastly, when a reviewer (aka user) attempts to close excel, the userform is displayed to prompt them to log out. There is a yellow text box in which the review’s name appears plus a message. This message box works in Excel 2013 but does not work in Excel 2010. Is there a code fix for this? User’s will be using excel 2010 and 2013 with this workbook.
The workbook is attached. When you open it and click on the button to display all the sheets, put in whatever fictional name you want (i.e. Mickey Mouse) and the uNID can be anything you want but it has to start with the letter “u” and have 7 numbers (i.e. u1111111, or u1234567) and then click start.
If someone can help me with these last two missing pieces, I can mark this thread as resolved and do a little happy dance!
Kaytie

P.S. There is some optional code with this workbook. The code is below. I know that I just need to remove the apostrophes to essentially activate it. I don’t quite understand what it will do so I don’t know if I want to use it or not. Can someone please explain what this code does?

Please Login or Register  to view this content.

Okay. I have made an interesting discovery on my quest to code the cancel button. This is the code that I currently have for the command button CANCEL:

Please Login or Register  to view this content.

When a user clicks on this button, a dialog box is displayed. The window is entitled "Microsoft Excel". The text in the box of the window reads "Do you want to save the changes you made to Login Sheet Template.xlm". The option buttons are Yes, No, Cancel. YES button saves the workbook and then closes excel application. The NO button closes the excel application. The CANCEL button closes the dialog box and allows the user to continue working on the workbook. Is there a way to have excel always choose CANCEL and not display the MS Excel dialog box?

This is odd. When a user opens up the workbook and clicks on the button to display sheets the userform appears prompting the user to input their name and employee ID. If the user click on the CANCEL button, the userform disappears and excel does not close. Why does the CANCEL button work like I want it to work upon a user log in but not when a user logs out?

AAAAAAARRRRGGGGGHHHHH! This is the most frustrating exercise in futility EVER! My head is bloody smashing it against this brick wall these past two days. Can anyone shed some light and give me a band aid? LOL

Cheers,
Kaytie

You all got nothing for me?
~Kaytie

Dear Kaytie!
Sorry for the delay.
Very few had the time. The forum seems to me that the topic has not yet been completed.
I do not see where the problem is it appropriate to use UserForm.
I hope the attached file is a solution.
Not sure I understand the problem. Do it for your remarks.
Help me if you write a short, simple sentences.
Sincerely Pan314