I have an idea

[MarkAndrews]

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

 

[DominicB]

Hi Mark

What about this :

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]

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]

Thanks - but the MSG Boxes appear the other way around Dominic?

Pennysaver - is there no way of getting round this?

 

[Oaktree]

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

 

[MarkAndrews]

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]

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]

Could i do this after the 2nd message box

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