I'm looking to develop a similar Excel spreadsheet as mentioned by Tim Mathis on 12/20/15. However all of the links are dead so I need to start from the beginning. I'd like to create a workbook where user1 will have access (password protected) to modify sheet1 and sheet2 and have read access only to sheet3, with user2 having read access only to sheet1 and sheet2 and write access to sheet3 and so on for multiple users and worksheets. And of course with an admin access to modify all spreadsheets. I've seen similar vba examples but none are quite what I'm looking for. Any suggestions? It seems like much of the above would work but the links no longer work. A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data. Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question. Anonymous 2017-10-20T00:06:03+00:00 Hi OssieMac, Thank you very much. Your workbook appears to have exactly what I've been looking for. I'll spend the next few days reverse engineering so that I better understand what each line of code does. Please don't be surprised if I reach back with questions. I agree with you on a shared workbook. I'm hoping that I can put this up in our SharePoint site (another project) so that is has to be checked out which in turn should avoid multiple users hitting it at the same time. But your advice is well taken and I'll be sure to step the process so multiple users aren't adding data at the same time. With respect to Access. In other cases it would be much better to use Access which I have a deeper knowledge of. However, because the base data is in multiple individual spreadsheets comprising many rows and columns of data that will change monthly, I thought Excel would be better suited. Additionally, because of the controls that we need to have in place, I'd rather have the user copy and paste the data so that I can avoid linking spreadsheets which brings with it it's own issues. Lastly, the reason for the multiple groups is because Group A & B will provide the data while Group C will be making decisions on what they can view on data submitted by A & B. I am extremely thankful for your efforts and helping me better understand how I can use code in Excel. Thank you again. Larry 0 comments No comments Thank you very much. Your workbook appears to have exactly what I've been looking for. I'll spend the next few days reverse engineering so that I better understand what each line of code does. Please don't be surprised if I reach back with questions. I agree with you on a shared workbook. I'm hoping that I can put this up in our SharePoint site (another project) so that is has to be checked out which in turn should avoid multiple users hitting it at the same time. But your advice is well taken and I'll be sure to step the process so multiple users aren't adding data at the same time. With respect to Access. In other cases it would be much better to use Access which I have a deeper knowledge of. However, because the base data is in multiple individual spreadsheets comprising many rows and columns of data that will change monthly, I thought Excel would be better suited. Additionally, because of the controls that we need to have in place, I'd rather have the user copy and paste the data so that I can avoid linking spreadsheets which brings with it it's own issues. Lastly, the reason for the multiple groups is because Group A & B will provide the data while Group C will be making decisions on what they can view on data submitted by A & B. I am extremely thankful for your efforts and helping me better understand how I can use code in Excel. Thank you again. OssieMac • 48,006 Reputation points • Volunteer Moderator 2017-10-19T23:32:48+00:00 Having individual signons would be ideal in that I could really lock this down and they could actually use their network name as a signon have their own passwords. This would give me the ability down the road, once I get this part figured out, to capture a general audit log to see who accessed the workbook over time. Just wishful thinking of future enhancements. Hi Larry, From your description of the project it appears that you want to use it as a shared workbook and I don't believe it is going to work satisfactorily but I am not an authority on Shared workbooks. This forum has numerous posts related to saving data with shared workbooks where if one user saves then the changes made by another user overwrites the changes etc. If the users are accessing the Excel workbook at different times then there should not be a problem but simultaneously accessing it will be problems. Please do some research on Shared Workbooks and even create a new thread on this forum and ask for more info (don't use this thread because it is not really on shared workbooks) and then make you own decision as to whether to use Excel. IMHO, the extent of the project you describe is unsuitable for Excel and you should be using Access or some other multiuser data base program where only the record that a user is accessing is locked and saving data is not a problem. Also these applications lend themselves to "Audit trails" which it appears you want to introduce at a later date. If Access is used then only the developer requires a full copy of Access. Microsoft provides a free "Run Time" program for distribution of the project to the users at the following link. https://www.microsoft.com/en-us/download/details.aspx?id=50040&751be11f-ede8-5a0c-058c-2ee190a24fa6=True&e6b34bbe-475b-1abd-2c51-b5034bcdd6d2=True&fa43d42b-25b5-4a42-fe9b-1634f450f5ee=True&a03ffa40-ca8b-4f73-0358-c191d75a7468=True However, having said all of that, I have uploaded a zipped example workbook to the following link. Unzip the workbook and open. I suggest that you first use the Admin and admin password (displayed on the first worksheet) and in particular view the matrix for the Users on the User Profiles sheet. https://1drv.ms/u/s!ArAXPS2RpafCkxplNSZ4uIvEFs5m Throughout I have used the Code names for worksheets so that if a user changes a sheet name then the code still works because the users will not be able to access the VBA project to change the CodeNames of the worksheets. The CodeNames are the names not in brackets in the Project Explorer (left column) of the VBA editor. Initially the VBA code will be locked. To Unlock Click on the plus sign to expand the tree in the left column and then enter the password ossie Then click the plus signs to expand the Project tree. The VBA project then remains unlocked until the workbook is closed and reopened. Guidelines to apply password protection to the VBA project. Right click the project name in the Project Explorer (left column in VBA editor) Select "VBAProject Properties" Select "Protection" tab. Check the box "Lock project for viewing" Enter password and confirm password Click OK Guidelines to remove password protection to the VBA project. Same as for applying the password but uncheck the box "Lock project for viewing" and then clear the "Password" and "Confirm password" fields. There is VBA code in the following modules. Module1 ThisWorkbook Sheet1 (Main Menu) Userform1 (Right click the userform and select "View Code") Following added with edit after initial posting. UDF in Module1. There is no direct way of converting or using the column header text to a worksheet object. The UDF code loops through the worksheets to match the code name to the required column header in the profile matrix and creates the worksheet object variable for the matching CodeName Referring to the Password textbox on the Userform, The displayed password character is created as follows. Set the font property to Wingdings Insert PasswordChar property by using use Alt and 0108 for the character. The character will display as a vertical line in the property field but is displayed as a dot on the Userform. Note that the Userform can be exported and imported into another Excel workbook. Select the Userform in the Project Explorer and then File -> Export and follow the prompts. (I usually navigate to the same folder as the project for the export). To import to another workbook, open the VBA editor and select File -> Import and follow the prompts. The Export and Import includes both the Userform and the Userform's code module. 0 comments No comments Having individual signons would be ideal in that I could really lock this down and they could actually use their network name as a signon have their own passwords. This would give me the ability down the road, once I get this part figured out, to capture a general audit log to see who accessed the workbook over time. Just wishful thinking of future enhancements. From your description of the project it appears that you want to use it as a shared workbook and I don't believe it is going to work satisfactorily but I am not an authority on Shared workbooks. This forum has numerous posts related to saving data with shared workbooks where if one user saves then the changes made by another user overwrites the changes etc. If the users are accessing the Excel workbook at different times then there should not be a problem but simultaneously accessing it will be problems. Please do some research on Shared Workbooks and even create a new thread on this forum and ask for more info (don't use this thread because it is not really on shared workbooks) and then make you own decision as to whether to use Excel. IMHO, the extent of the project you describe is unsuitable for Excel and you should be using Access or some other multiuser data base program where only the record that a user is accessing is locked and saving data is not a problem. Also these applications lend themselves to "Audit trails" which it appears you want to introduce at a later date. If Access is used then only the developer requires a full copy of Access. Microsoft provides a free "Run Time" program for distribution of the project to the users at the following link. https://www.microsoft.com/en-us/download/details.aspx?id=50040&751be11f-ede8-5a0c-058c-2ee190a24fa6=True&e6b34bbe-475b-1abd-2c51-b5034bcdd6d2=True&fa43d42b-25b5-4a42-fe9b-1634f450f5ee=True&a03ffa40-ca8b-4f73-0358-c191d75a7468=True However, having said all of that, I have uploaded a zipped example workbook to the following link. Unzip the workbook and open. I suggest that you first use the Admin and admin password (displayed on the first worksheet) and in particular view the matrix for the Users on the User Profiles sheet. https://1drv.ms/u/s!ArAXPS2RpafCkxplNSZ4uIvEFs5m Throughout I have used the Code names for worksheets so that if a user changes a sheet name then the code still works because the users will not be able to access the VBA project to change the CodeNames of the worksheets. The CodeNames are the names not in brackets in the Project Explorer (left column) of the VBA editor. Initially the VBA code will be locked. To Unlock Click on the plus sign to expand the tree in the left column and then enter the password ossie Then click the plus signs to expand the Project tree. The VBA project then remains unlocked until the workbook is closed and reopened. Guidelines to apply password protection to the VBA project. Right click the project name in the Project Explorer (left column in VBA editor) Select "VBAProject Properties" Select "Protection" tab. Check the box "Lock project for viewing" Enter password and confirm password Guidelines to remove password protection to the VBA project.

Same as for applying the password but uncheck the box "Lock project for viewing" and then clear the "Password" and "Confirm password" fields. There is VBA code in the following modules. Sheet1 (Main Menu) Userform1 (Right click the userform and select "View Code") Following added with edit after initial posting. UDF in Module1. There is no direct way of converting or using the column header text to a worksheet object. The UDF code loops through the worksheets to match the code name to the required column header in the profile matrix and creates the worksheet object variable for the matching CodeName Referring to the Password textbox on the Userform, The displayed password character is created as follows. Set the font property to Wingdings Insert PasswordChar property by using use Alt and 0108 for the character. The character will display as a vertical line in the property field but is displayed as a dot on the Userform. Note that the Userform can be exported and imported into another Excel workbook. Select the Userform in the Project Explorer and then File -> Export and follow the prompts. (I usually navigate to the same folder as the project for the export). To import to another workbook, open the VBA editor and select File -> Import and follow the prompts. The Export and Import includes both the Userform and the Userform's code module. Anonymous 2017-10-19T12:05:24+00:00 OssieMac, I should have added that I would be happy to create the base workbook if you could provide the direction on the code to use to do this. I'd just need a location to drop it to. I don't mind being in the weeds which in turn helps me expand my knowledge. Thanks, Larry 0 comments No comments I should have added that I would be happy to create the base workbook if you could provide the direction on the code to use to do this. I'd just need a location to drop it to. I don't mind being in the weeds which in turn helps me expand my knowledge. Anonymous 2017-10-19T10:32:44+00:00 Hi OssieMac, Thank you for replying. Here is a brief overview of what I'm trying to create. This workbook will provide worksheets where users can add data (Group C) which will then be aggregated in scorecards (Group B) which are then used in another worksheet for aggregating scores (Group A). Each group is independent of the other and while they will be enabled to see each group's work they will not be able to modify each group's work. Then I also need full read access to some users (Group D) and full author access for those inevitable changes (Group E). We need to be able to have controls in place preventing unauthorized changes, thus the security. There will be approximately 17 pages, Home, Summary, pages A1-A5, pages B1-B5 and pages C1-C5. but they should be able to be added to or subtracted from based on the final design. From a security standpoint with each group will use a password to access their pages. Although I was wondering if I could just use the general Read Only security on the workbook for Group D? I don't mind that everyone else would have to use a password to access the workbook for changes. Sort of a two level security. From an access standpoint with R/W being Read/Write and R being Read only: Group A - R/W Pages A1-A5, R all other pages. Group B - R/W Pages B1-B5, R all other pages Group C - R/W Pages C1-C5, R all other pages Group D - R all pages Group E - R/W all pages Having individual signons would be ideal in that I could really lock this down and they could actually use their network name as a signon have their own passwords. This would give me the ability down the road, once I get this part figured out, to capture a general audit log to see who accessed the workbook over time. Just wishful thinking of future enhancements. I hope this all makes sense and I certainly appreciate any help and direction that you can provide. Thank you, Larry 0 comments No comments Thank you for replying. Here is a brief overview of what I'm trying to create. This workbook will provide worksheets where users can add data (Group C) which will then be aggregated in scorecards (Group B) which are then used in another worksheet for aggregating scores (Group A). Each group is independent of the other and while they will be enabled to see each group's work they will not be able to modify each group's work. Then I also need full read access to some users (Group D) and full author access for those inevitable changes (Group E). We need to be able to have controls in place preventing unauthorized changes, thus the security. There will be approximately 17 pages, Home, Summary, pages A1-A5, pages B1-B5 and pages C1-C5. but they should be able to be added to or subtracted from based on the final design. From a security standpoint with each group will use a password to access their pages. Although I was wondering if I could just use the general Read Only security on the workbook for Group D? I don't mind that everyone else would have to use a password to access the workbook for changes. Sort of a two level security. From an access standpoint with R/W being Read/Write and R being Read only: Group A - R/W Pages A1-A5, R all other pages. Group B - R/W Pages B1-B5, R all other pages Group C - R/W Pages C1-C5, R all other pages Group D - R all pages Group E - R/W all pages Having individual signons would be ideal in that I could really lock this down and they could actually use their network name as a signon have their own passwords. This would give me the ability down the road, once I get this part figured out, to capture a general audit log to see who accessed the workbook over time. Just wishful thinking of future enhancements. I hope this all makes sense and I certainly appreciate any help and direction that you can provide. OssieMac • 48,006 Reputation points • Volunteer Moderator 2017-10-19T06:07:14+00:00 I have only just seen where you tacked onto to the thread by Tim Mathis but as you have done here, it is better to start a new thread anyway. You need to understand that AFAIK there is no high level of security that can be used for your proposed project. I can only create a system that suppresses inadvertent changes by honest users. I am not going to go into it in detail here but anyone with low to intermediate computer literacy can get around the protection. Some questions/comments first: Approximately how many worksheets will be in the workbook? Will multiple users need write access to the same worksheet/s and if so, do you want them to have separate passwords? I have not had any success with sharing workbooks that need to be saved while the workbook is still in use by multiple users so if this is the case, I am not sure I can help. However, if multiple users and they do not need to access simultaneously then it should be OK. 0 comments No comments I have only just seen where you tacked onto to the thread by Tim Mathis but as you have done here, it is better to start a new thread anyway. You need to understand that AFAIK there is no high level of security that can be used for your proposed project. I can only create a system that suppresses inadvertent changes by honest users. I am not going to go into it in detail here but anyone with low to intermediate computer literacy can get around the protection. Some questions/comments first: Approximately how many worksheets will be in the workbook? Will multiple users need write access to the same worksheet/s and if so, do you want them to have separate passwords? I have not had any success with sharing workbooks that need to be saved while the workbook is still in use by multiple users so if this is the case, I am not sure I can help. However, if multiple users and they do not need to access simultaneously then it should be OK.