Changing ThisWorkbook.ChangeFileAccess 
Author Message
 Changing ThisWorkbook.ChangeFileAccess

Hi Everyone

I'm trying to create a file that will always open as read
only unless opened by users with a specific username.
To achieve this I have used the following code.

Sub Auto_Open
If Application.UserName <> "mattormark" Then
        ThisWorkbook.ChangeFileAccess xlReadOnly
End If
End Sub

Unfortunately this spreadsheet can be called by a toolbar
which opens the spreadsheet without running the Auto_Open.
I have amended all my toolbar macros so that they first
run Auto_Open. Unfortunately this doesn't work as I don't
know whether the the workbook has been opened as ReadOnly
or ReadWrite. So half the time the macro returns an error:

Method 'ChangeFileAccess of Object'_Workbook failed.

My solution is to check the status of the workbook first.
e.g.

Sub Auto_Open
If Application.UserName <> "mattormark" Then

    ReadorWrite = ActiveWorkbook.ChangeFileAccess.Mode

    If ReadorWrite = ReadWrite Then
        ThisWorkbook.ChangeFileAccess xlReadOnly
    End If

End If
End Sub

This returns a compile error on ChangeFileAccess.
Can someone tell me what the code should be to assign the
status of the file (ReadOnly or ReadWrite) to my
ReadorWrite Variable

Thanks in advance

Matt



Mon, 24 May 2004 23:25:00 GMT  
 Changing ThisWorkbook.ChangeFileAccess
Your code should be something like this:

    If ActiveWorkbook.ReadOnly = False Then
        ThisWorkbook.ChangeFileAccess xlReadOnly
    End If

--
Jim Rech
Excel MVP



Tue, 25 May 2004 01:05:42 GMT  
 Changing ThisWorkbook.ChangeFileAccess
Thanks Jim

Do you know of any way I can stop the "is being modified"
warning message coming up?

Regards

Matt

Quote:
>-----Original Message-----
>Your code should be something like this:

>    If ActiveWorkbook.ReadOnly = False Then
>        ThisWorkbook.ChangeFileAccess xlReadOnly
>    End If

>--
>Jim Rech
>Excel MVP

>.



Sat, 29 May 2004 02:14:01 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. How change ThisWorkbook code via macro?

2. ChangeFileAccess Saves File ?? Curious

3. Compiling error in hidden module: ThisWorkBook

4. Installing a counter within the Thisworkbook module?

5. Calling a program in "ThisWorkbook"

6. Misc_String = ThisWorkbook.VBProject.Description

7. ThisWorkbook

8. Delete ThisWorkbook code

9. Thisworkbook copy

10. Insert code into ThisWorkbook module


 
Powered by phpBB® Forum Software © phpBB Group