I have an idea

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,970
Office Version
  1. 2010
Platform
  1. Windows
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
Thanks - but the MSG Boxes appear the other way around Dominic?

Pennysaver - is there no way of getting round this?
 
Upvote 0
Why not just restrict access to the file/folder all together? Your IT department should be able to help with that...
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,756
Messages
6,144,301
Members
450,536
Latest member
DavidRankin

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
Back
Top