I am trying to unlock cells in a worksheet by comparing the current username to the username(s) of the person(s) who entered the data in that column of the worksheet and not allow them to enter in that cell unless it does not match. There is a workbook with a worksheet “Audit Trail”, which contains in in the same rows of columns A, B and D, respectively, the cell address, the worksheet name and the username of who entered the data in the cell specified in columns A & B of that row. I add the worksheets that contain the cell that I want to unlock to this workbook, then save as a new workbook. The code would need to find the worksheet name that matches the cell to be unlocked in the “Audit Trail” worksheet, then select the cell addresses that have the same column as the cell to be unlocked and then finally comparing the username(s) to the current user and if there are no matches, unlock the cell. There are 2 additional problems with the way it is currently set up: 1st- some of the worksheets that are added to the workbook have multiple pages that are one below another – so I will probably have to change these so that the pages are side-by-side instead so that each column is on only one page; 2nd – in the worksheets that are added to the workbook the cells that need to be unlocked might be in different locations on the worksheet, so maybe if I set up a range with a consistent name for the cells that I want unlocked would work. If anyone has any ideas how to do any of this please let me know. I have attached a workbook with an Audit Trail worksheet and 2 lot worksheets below along with the current code that is use in this workbook.
If you want to know the background I had 3 threads running previously, which intertwined and led to this thread. If interested you can look at those threads, but I’m not checking them anymore for new posts. They are: “MessageBox error and adding it to a Beforesave to an audit trail” , “Lock cells in worksheet after they are changed” and "Beforesave selecting all worksheets except one". These and posts and in other forums have brought me to my current workbook, which has a workbook code and a Userform1 code for a ComboBox. This workbook records all changes in the worksheets except the “Audit Trail” worksheet in the “Audit Trail” worksheet in columns A through H. These columns list the following information for any cell that is changed in the other worksheets in the workbook in order from A - H: Cell Address, Worksheet Name, Date and Time of the Change, the User Name of the User that Made the Change, the Previous Value of the Cell, the New Value of the Cell, the Reason for the Change and if the Cell is Locked. The workbook code is as follows:
The userform1 code is as follows:
the workbook is attached below. Please help if you can. Thank you in advance.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks