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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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
8,015
Office Version
  1. 365

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
 

Forum statistics

Threads
1,141,012
Messages
5,703,729
Members
421,312
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top