Getting user to log changes before workbook is closed.

[Belinda]

I have a workbook that is used by two different members of the same team.
I would like to use VBA so that before the workbook is closed, the following actions take place:

1> If the workbook has been modified, the following message comes up to ask the user to confirm whether he wants to save the changes.
2> If “Yes” is selected, a message box comes up asking the user to enter his own password.
3> When the user enters his password, he is directed to an existing worksheet called “Log Update”.
4> The Log Update has a table with three columns with the headers:
i) Log Update (Column A)
ii) Initials (Column B)
iii) Notes (Column C)

5> The cursor goes to the last empty row of the table. Current date is automatically added in Column A and the User’s initials are automatically added to Column B.

6> Information on Initials and corresponding passwords are:
AS (Initial) > Bi247 7 (Password)
CL (Initial) > Sal9865 (Password)

7> After the user has entered information in Column C, a message comes up asking the user whether he wants to add another record or, save and close the workbook.
i) If user chooses to add another record, cursor goes to next row, adds the current date and initials and user enters notes in Column C.
ii)This step is repeated until the user decides to save and close the workbook.

Thank you for your help.

[mdmackillop]

How about a Userform solution. If preferred, the Log Sheet can be hidden.

[Belinda]

Excellent idea regarding the Userform!
I'v tried it and it works great.

I would like to add an extra step at the beginning so that when the user clicks the close button after changes have been made to the workbook, a message automatically comes up:
"Please log in the changes made to the workbook", and the UserForm appears.

If no changes have been made to the workbook, then no message or UserForm will appear and the workbook closes.

[mdmackillop]

Try this

[Belinda]

Thank you, mdmackillop. Much appreciated!