VBA - check if user name matches requirements, if not, dont open

**galvinpaddy** · 06-16-2015, 11:50 AM

afternoon all!

I think i've posted something similar here before but i can't find it through the 'My Posts' option, so please forgive me for asking.

I have the following:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Selection.NumberFormat = "m/d/yyyy"
    Range("C2").Value = Environ("Username")
Application.ScreenUpdating = True
End Sub

How can i (if i even can) get my VBA to check the usernam that is trying to open the spreadsheet, and if it is one of 3 (for example) then the spreadsheet will open.
If the username does not match one of the threee names, i dont want the sheet to open.

Don't mind at present if the names are defined either in the code itself or the code reads from a list on one of the tabs.

I know i can put a password in to prevent the sheet being opened, but at present i dont want to follow that route, so thought i would ask if the above is possible.

Many thanks in advance.

Rich

**mikeTRON** · 06-16-2015, 12:06 PM

Why not lock it down with a password and avoid this?

**nigelbloomy** · 06-16-2015, 12:18 PM

I usually use the following code and then do an if statement in the on open event.

Public Function GetUserName() As String
    GetUserName = Environ("USERNAME")
End Function

**galvinpaddy** · 06-16-2015, 12:23 PM

MikeTRON - Agreed, it is a way to do it, but at present i'm trying to delete all passwords from opening sheets.
Nigelbloomy - Thanks, i'll give that a go, could you provide a little more detail on how the code will work?

**nigelbloomy** · 06-16-2015, 12:42 PM

You could use it like this

Public Function GetUserName() As String
    GetUserName = Environ("USERNAME")
End Function

Sub testNames()
Select Case GetUserName
    Case "Nigel": Exit Sub
    Case "Bill": Exit Sub
    Case Else: ThisWorkbook.Close
End Select
End Sub

**galvinpaddy** · 06-16-2015, 02:04 PM

potentially a daft question incoming :D

I dont understand how i can get the code to work upon opening the sheet.

If for example the usernames i want to be able to open the spreadsheet are:
Dduck
Wcoyote
Bbunny

then how do i incorporate that into the

Private Sub Workbook_Open()

End Sub

Either with those usernames being hard coded into the macro or with the usernams being entered into a sheet within the workbook?

**nigelbloomy** · 06-16-2015, 02:23 PM

Just a warning. If someone holds shift while opening the file, the code will not run when the file opens. That can be helpful for you to know while troubleshooting and making sure it works for the correct people. Just know that someone can get around the code if they do a little Google searching. I would recommend the password option if you want a little more security. That can easily be broken too, but it takes a little more than just pressing shift while opening the file.

http://www.excelforum.com/excel-prog...ng-macros.html

**nigelbloomy** · 06-16-2015, 02:14 PM

This would work. I just added in to close without saving changes. Otherwise the user could click cancel and still use the workbook.

Private Sub Workbook_Open()
Dim GetUserName As String

GetUserName = Environ("USERNAME")
Select Case GetUserName
    Case "Nigel": Exit Sub
    Case "Bill": Exit Sub
    Case Else: ThisWorkbook.Close SaveChanges:=False
End Select
End Sub

**galvinpaddy** · 06-16-2015, 02:28 PM

Great stuff, thanks alot.
Not overly concerned about people googling a way to get round it, when i get to that stage i'll have to work something else out :D
Thanks an awful lot for the code and also the link, good to know!

Rich

VBA - check if user name matches requirements, if not, dont open

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA - check if user name matches requirements, if not, dont open

Re: VBA - check if user name matches requirements, if not, dont open

Re: VBA - check if user name matches requirements, if not, dont open

Re: VBA - check if user name matches requirements, if not, dont open

Re: VBA - check if user name matches requirements, if not, dont open

Re: VBA - check if user name matches requirements, if not, dont open

Re: VBA - check if user name matches requirements, if not, dont open

Re: VBA - check if user name matches requirements, if not, dont open

Re: VBA - check if user name matches requirements, if not, dont open

Thread Information

Users Browsing this Thread

Similar Threads

Check and list names of all workbooks that already open by another user

VBA to check if user has pdf file open

if worksheet already open then dont open again

[SOLVED] If value in Column A matches K, enter value in Column O into H, dont do if F is empty

VBA Spell Check - Dont ask to check rest of Doc?

Bookmarks

Bookmarks

Posting Permissions