Allow diiferrent users to access different ranges to input data

Hi All

I created a worksheet (attached) using Excel 2010. The worksheet “January” has been format for data input from a dropdown menu and conditional formatting corresponding to a legend in the worksheet. So far all okay, I have completed my worksheet on that basis.

The next step is where I encounter the problem: I intend to share this workbook so that at any time any of the 10 users can open the file and input their own data into the worksheet. To do this, I use “Review”, “Allow users to Edit Ranges”. In order to prevent conflict of data during input, I am password protecting each of the ranges that each of them can excess; e.g. user NWP will only be allowed to input data into the green cells in row 19 where marked “Avail”- after inputing her password “NWP”

I now wish to do the same thing for each of the 10 users using their initial as their individual password. I found that I cannot simply copy and paste from the row 19 to the other rows as the ranges in the menu “Review”, “Allow users to Edit Ranges” use absolute ranges and not relative ranges.

In my example, I have used 10 users only but in reality I may have up to 300 rows (users).

Can someone help me resolve this please, with a macro perhaps? I am only a basic user. Many thanks.

Thanks again for the quick response. Yes, I think your suggestion should work as long as each user can only edit their respective row against their user name.

I would like to try out what you say but as mentioned I am not familiar with macros and VBA. Can you help by providing me with a step by step instruction on what you meant by "If you adjust the code as below and just put their (windows)usernames in the sheet, they will only be able to adjust the row which holds their username" For this purpose, I would like to use the usernames as indicated in the attached worksheet. What about the other months if I need to create a similar worksheet on a different page. How do I do this? Much appreciated.

Originally Posted by boonfootan

Thanks again for the quick response. Yes, I think your suggestion should work as long as each user can only edit their respective row against their user name.

I would like to try out what you say but as mentioned I am not familiar with macros and VBA. Can you help by providing me with a step by step instruction on what you meant by "If you adjust the code as below and just put their (windows)usernames in the sheet, they will only be able to adjust the row which holds their username" For this purpose, I would like to use the usernames as indicated in the attached worksheet. What about the other months if I need to create a similar worksheet on a different page. How do I do this? Much appreciated.

Hi JasperD

I tried what you said based on my understanding of what you wrote above and it worked: I just copied and paste the code you wrote into the white space after pressing Alt+F11. Then saved the file and it worked. I think the important thing is for the user names in column A to match the respective users' Windows login names before access will be given for that particular row for data input.

Now what do I do if I need to disable the code temporarily to make changes to the users' names (add or delete user names in column A) and to re-activate the code after the edits. Is there any keystroke that I can press to toggle the code on and off?

Once again many thanks. Much appreciated

Originally Posted by boonfootan

Hi JasperD

I tried what you said based on my understanding of what you wrote above and it worked: I just copied and paste the code you wrote into the white space after pressing Alt+F11. Then saved the file and it worked. I think the important thing is for the user names in column A to match the respective users' Windows login names before access will be given for that particular row for data input.

Now what do I do if I need to disable the code temporarily to make changes to the users' names (add or delete user names in column A) and to re-activate the code after the edits. Is there any keystroke that I can press to toggle the code on and off?

Once again many thanks. Much appreciated

Sorry, JaperD: another question arose as I was testing this code out. Appears that it works only for January Sheet5 as I only uploaded this worksheet for your advice initially. Sheet5 is just one worksheet of a larger workbook with all of the 12 months on a separate worksheet each with a similar layout. How do I get this to work on the other sheets in the same workbook in addition to the query I have on toggling the code on and off above for editing purpose. Thanks for the help.

Hi JasperD

I have tried to include the additional codes in accordance with your latest post but I am not sure if my understanding is correct. I have therefore attached the actual excel file I am working on here for you to take a look for me to confirm what I have done is correct.

The other thing that I am not sure of is the toggle on/off codes which I have not included in the workbook. I am not sure where to put these codes. Can you help me do that please. Also after inclusion of the toggle on/off codes, how do I activate it? Which key strokes to activate/deactivate the codes?

Also the password “excelforum.com” in the worksheet protection code – when do I need to key this in?

I can see the “Enable Macro” box sometimes comes on, and sometimes off – why is that?

The purpose of the excel file:
a. I will upload this file onto a network as a shared but protected excel file.
b. I will toggle the code on to activate the excel file.
c. Users will go in separately at different times to input data in the relevant row that corresponds to their user names before a given deadline.
d. After the deadline, I will unshare the file.
e. I will then need to toggle off the code, to make any necessary changes to the file, e.g. adding new users and deleting users who are no longer in the list.
f. Then toggle on the file again.

My apologies for being such a basic excel user with so little macro knowledge but I really appreciate your help in this, without which I would not have known how to proceed. Much thanks, JasperD.