User Permissions VBA

**jimmy474** · 07-23-2008, 02:54 PM

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** · 07-23-2008, 03:03 PM

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

Code:

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.

**jimmy474** · 07-23-2008, 03:10 PM

Originally Posted by wmorrison49

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** · 07-23-2008, 03:12 PM

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** · 07-24-2008, 08:42 AM

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

Code:

Dim sName As String
sName = Environ("UserName")
MsgBox sName
Range("A1") = sName

**wmorrison49** · 07-24-2008, 09:16 AM

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** · 07-24-2008, 09:28 AM

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.

Thread: User Permissions VBA

Thread Tools

Display

User Permissions VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions