User Permissions VBA

[jimmy474]

I am trying to assign user permissions to different sheets within my excel workbook.
The way it should work is as follows:
The file should only be accessed by 6 people.

Sheet 1 should be accessed by Staff members A and B
Sheet 2 should be accessed by Staff members B, C, D and E

The way I am thinking is that the Excel file retrieves the Windows usernames of the user's and then allows them to access the file based on the requirements above.

Can anyone please help as I am clueless.

[wmorrison49]

Here's some code that takes the Username on the computer and puts it into cell A1 on the currently open tab.

Please Login or Register  to view this content.

You can have this fill into any cell of course. From there, you can run something that will protect Sheet1 or Sheet2 based on what username is in that cell with a password that only you know. That would work as a way to insure that only certain people get to see the tab you want them to.

[jimmy474]

Here's some code that takes the Username on the computer and puts it into cell A1 on the currently open tab.

Dim Name as String
Name = Application.UserName
Range("A1") = Name

You can have this fill into any cell of course. From there, you can run something that will protect Sheet1 or Sheet2 based on what username is in that cell with a password that only you know. That would work as a way to insure that only certain people get to see the tab you want them to.

Thanks for that - but the username doesn't seem to appear in the cell

[wmorrison49]

That's weird. I have attached a workbook that is working for me. Just open it up and run the ShowName macro. On my computer, "Will" pops up in A1. Hopefully this will work for you.

[mudraker]

wmorrison49

Please make sure in future you follow forum rules & wrap your VBA code - see rule 3 of the forum rules


Now to the problem

The application user name can have any type of entry in it - Include just a space and is completly independant of the Windows login ID
Have a look at Tools > Options > General Tab > User Name

To use the Windows Login ID

Please Login or Register  to view this content.

[wmorrison49]

I am a little confused. Where is the code I didn't wrap? I only put in the three lines of code on my first post.

[mudraker]

The 3 lines of code is what you needed to wrap

This forum has a 2 hour time limit on you be able to edit your thread/posting.
As it was mote than 2 hours since you had posted the reply I edited your posting & wrapped the VBA code for you.