How set ReadOnly with vba?

[backup69]

Hi

If user remove readonly from the file property i want do something like this.

If ActiveWorkbook.Name = "Blank.xls" then
ActiveWorkbook ReadOnly:=True
but this Readonly stuff not working, i can check only
(If ActiveWorkbook.ReadOnly Then) but i need set it back if user remove readonly from the "Blank.xls"

 

[Ahnold]

The ReadOnly property will only allow you to check whether the workbook is ReadOnly or not. It won't allow you to set the property. You have to use the SaveAs Method to set the ReadOnly property. Go to the help section in VBA and type in "SaveAs Method" and it will show the syntax.

 

[julyannjackson]

Set Read Only Property

You can use this code to set the ReadOnly property

SetAttr pathname, attributes

To set ReadOnly=True
SetAttr "C:\Blank.xls", vbReadOnly

To set ReadOnly=False
SetAttr "C:\Blank.xls", vbNormal

 

[hatman]

It is worthy of note that SetAttr cannot be used with an open file. The following code will NOT work as expected, and should be avoided.

Sub stuff4()

    SetAttr ThisWorkbook.FullName, vbNormal
    
    ThisWorkbook.Save

End Sub

 

[Smitty]

You can use the ChangeFileAccess method. Here's an example:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> SetAsReadOnly()
    <SPAN style="color:#007F00">'   Test for PC User Name</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strUser <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
        strUser = Environ("USERNAME")
        
         <SPAN style="color:#007F00">'  Set Read only File Access for each Office's specific version</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> strUser
            <SPAN style="color:#007F00">'   Full Workbook Access</SPAN>
            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = "YourUserName", "AnotherUser"
                <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook.ReadOnly Then _
                    ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite, WritePassword:="admin"
            <SPAN style="color:#007F00">'   Limit Access</SPAN>
            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <> "YourUserName"
                <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> ActiveWorkbook.ReadOnly Then _
                    ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly, WritePassword:="admin"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

HTH,

Smitty

 

[hatman]

Yeah, I found your code, Smitty, on another post, as I parsed through the most recent posts containing the keywork Readonly.

I had simply posted my 2 cents here so others wouldn't get lead down a dead end, like I did. The SetAttr has limitations, which the previous poster hadn't mentioned.

Thanx for giving a more viable option here... I was actually about to post a link to your other post so people stumbling in here would find there way to another optional solution... but you got here first <|;O)

Still doesn't help me in my own problem, which is more complex. The ChangeFileAccess method still does some funky things, potentially. It yields some interesting dialog boxes under certain circumstances... some of which I can't quite dismiss. The part where it potentially Re-Opens the file from disk has me very concerned, for my application.

If you get a chance, I tried to explain some of my needs in this post. Not as clearly defined as I would like it... but a place to start.

 

[AlexM]

I see, that SetAttr works. For example this code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
SetAttr Me.FullName, vbReadOnly
End Sub

sets read-only attribute on this file.

 

[hatman]

The Help file explicitly states:

A run-time error occurs if you try to set the attributes of an open file.

But on further testing, I see that this statement would probably be more accurate if written

A run-time error occurs if you try to set the attributes of an file open in another application.

Alex: you are correct in your assertion, and I was incorrect in my above statement... my code DOES function as intended. I see now that my initial test errored out for completely different reasons (that I won't get into here).

 

[tomcatonnet99]

The Help file explicitly states:



But on further testing, I see that this statement would probably be more accurate if written



Alex: you are correct in your assertion, and I was incorrect in my above statement... my code DOES function as intended. I see now that my initial test errored out for completely different reasons (that I won't get into here).

So does set attrib work in open workbook event to toggle between read only and read write? I need to make macro enabled workbook to be readonly for All except admin
Also admin has a cmdbtn to click which asks for password, and if successful then file should be set to readwrite property.

Further when in readonly, I need users to have drill down ability for pivottables ( will readonly prevent that??)
Thanks

 

[tomcatonnet99]

Change file access method wasn't reliable, tried with few users on network drive, all in readonly mode on opening file, and only one user to go readwrite... Gives msgs such as querying if earlier version could be discarded? , at times notified of file being readonly, pretty random!

Is there a solution ??