I have an idea

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
I am using this code in Workbook_open event

MsgBox "Welcome " & Application.UserName

I would like excel to look at the username in this msgbox & then decide if it can be opened by a defined user list - if the name in the msgbox isn't on the list, msgbox "you do not have permission to view this document"

Is it possible?

TIA
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Hi Mark

What about this :

Code:
Sub test()
Dim users(2)
users(1) = "Dominic Brown"
users(2) = "Mark Andrews"
For n = 1 To 2
If Application.UserName = users(n) Then
MsgBox "You're OK..."
Exit Sub
Else
MsgBox "...but you're not"
End If
Next n
End Sub

Obviously, you just need to change the MsgBox with the actions you want to do when a user is permitted (or not).

HTH

DominicB
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Sure, if you populate your list, you can use the Find method (detailed in the VBA helpfile) to search for a match. If a match isn't found, close the WB.

Note that nothing will stop users from setting security to High and disabling the event.

HTH,

Smitty
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Thanks - but the MSG Boxes appear the other way around Dominic?

Pennysaver - is there no way of getting round this?
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984

ADVERTISEMENT

Why not just restrict access to the file/folder all together? Your IT department should be able to help with that...
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Why not just restrict access to the file/folder all together? Your IT department should be able to help with that...

I was thinking that, but was curious as to if my idea would work
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Maybe something like this:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">Dim</SPAN> CurrentUser <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, FirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
        CurrentUser = LCase(Environ("UserName"))
        
        <SPAN style="color:#00007F">With</SPAN> Sheets(1).Range(Cells(1, 1), Cells(Rows.Count, "A").End(xlUp))
            <SPAN style="color:#00007F">Set</SPAN> c = .Find(CurrentUser, LookIn:=xlValues)
            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
                FirstAddress = c.Address
                <SPAN style="color:#00007F">Do</SPAN>
                    MsgBox "Authorized to Proceed"
                    <SPAN style="color:#00007F">Set</SPAN> c = .FindNext(c)
                <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c.Address <> FirstAddress
            <SPAN style="color:#00007F">Else</SPAN>
                MsgBox "Unauthorized to Proceed"
                <SPAN style="color:#007F00">'   ActiveWorkbook.Close False</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Could i do this after the 2nd message box

Code:
Public Sub test()
Dim users(2)
users(1) = "Dominic Brown"
users(2) = "Mark Andrews"
For n = 1 To 2
If Application.UserName = users(n) Then
MsgBox "You are not authorised to view this document"
Exit Sub
Else
MsgBox "You are authorised to view this document"
Application.Close
End If
Next n
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,113,795
Messages
5,544,337
Members
410,603
Latest member
rseckler
Top