File Open - force Read Only for specified users

[Gerald Higgins]

Hi all, did a search, found this
http://www.mrexcel.com/forum/showthread.php?t=370089&highlight=force+read
which got me half way there, but looking for help on the last bit.

I've got a file which I would like to set up so that certain specified users can only open it as read only, while other specified users can open a read/write version.

I don't really mind which way round this works - either I would specify the read only users, and all others would be read/write by default, OR I'd be happy to specify the users who could read/write, and all others would be read only by default.

I adapted the code in that other thread to this,

If Not ActiveWorkbook.ReadOnly Then 
ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly

which works. I've incorporated it into the Auto Open routine, and it correctly makes the file read only.

What I want to do is add something that says effectively...

If user = John Smith, Jane Brown
open the file as read only
else

and I need help with the bit in bold.

Any ideas ?

Thanks in advance...

 

[*shudder*]

If each user has their own computer/logon then you can set an if/case statement for different users and extract the username/logon ID using VBA.

If users can use any machine and access the workbook then you will need to set up some sort of logon userform when the workbook opens.

 

[Gerald Higgins]

Thanks Shudder I'll try that later today and let you know how I get on. Cheers.

 

[Gerald Higgins]

It worked !

Here's the code that I ended up with.
This makes it read only for everyone except me.
Inserted at the start of the Auto Open routine.

Select Case Application.Username
Case "Gerald Higgins"
GoTo bypass
Case Else
If Not ActiveWorkbook.ReadOnly Then ActiveWorkbook._
        ChangeFileAccess Mode:=xlReadOnly
bypass:
End Select

Thanks to *shudder* for the pointer !

 

[ZacCerrato]

Is there a way to test for my user name?

 

[ZacCerrato]

**Bump**

 

[ZacCerrato]

Okay, I've inserted:

Sub Auto_Open()
    Select Case Application.Username
    Case "ZacCerrato"
    GoTo bypass
    Case Else
    If Not ActiveWorkbook.ReadOnly Then ActiveWorkbook. _
        ChangeFileAccess Mode:=xlReadOnly
bypass:
    End Select
End Sub

Into my worksheet. This has not prevented co-workers from opening the file (which is saved on a shared drive) as a read/write file. It does not force them to use the file as a read-only file.

Any suggestions of why this may be?

 

[ZacCerrato]

Is the "GOTO bypass:" and "bypass:" really necessary? Doesn't it mess this procedure up?

 

[Gerald Higgins]

One way to test for user name . . .

MsgBox ("Current user is " & Application.Username)

 

[Gerald Higgins]

I'm still a beginner on VBA, so there are probably better ways of doing what I wanted to achieve.
But the code I posted does do what I wanted - it lets me open the file as read/write (as long as no-one else has already got it open) and it opens the file as read only if anyone else opens it.

One thing to watch out for - if users choose not to enable macros, then the auto open routine won't run, and in that event other users will be able to open a read/write version.
But if they do enable macros, this should do what it says.

In my application, all the users are in the habit of enabling macros because they have to do that to do anything useful with it.