Make an entire workbook "read only" based on the value in a specific cell

[dsheard2015]

Hello,

I have a very large workbook with approximately 560 worksheets. When the workbook opens, I have a login userform that pops up where the user will enter their username and password. That login info is used to determine which sheets in the workbook can be accessed. All the sheets in the workbook are protected and all sheets have locked and unlocked cells. After the workbook is accessed, the current login username is entered on the master index page in cell 'MASTER INDEX'!AD5.

What I am trying to figure out is how I can make the entire workbook "read only" or non-editable if the name located in 'MASTER INDEX'!AD5 is contained within a named range, "Commander_Login". Obviously, if the name is not in that range then the workbook would need to work as normal.

I'm hoping this is possible. Any help with this is greatly appreciated!

Thanks,

Dave

 

[Jaafar Tribak]

You could make the workbook ReadOnly with a function like this :

Function MakeWorkbookReadOnly(ByVal Wb As Workbook) As Boolean

    With Wb
        If Not .ReadOnly Then
            .Saved = True
            .ChangeFileAccess xlReadOnly
        End If
        MakeWorkbookReadOnly = .ReadOnly
    End With

End Function

Usage example :

Sub Test()

    If MakeWorkbookReadOnly(ThisWorkbook) Then
        MsgBox ThisWorkbook.Name & " is now ReadOnly"
    End If

End Sub

 

[dsheard2015]

Hello Jaafar,

Thank you for your post! I am fairly new to vba and though I have a lot within this workbook, I am still learning. I haven't come across one like what you sent me, where do these codes get installed please?

I am very anxious to hear back so that I can see how they work with my workbook.

Thanks again,

Dave

 

[Jaafar Tribak]

Hi,

Just add a new Standard Module to your VBProject and place in it the two codes I posted .

then if you run the Test Macro, the current workbook should become Read Only.

 

[dsheard2015]

Jaafar,

Very nice! It works great! Now, how would I apply this to my workbook? When the workbook is opened a login userform pops up where the user needs to enter their username and password to gain access to the sheets they have been given permission to access. I have several logins that should only have "read only" access so how can I incorporate your codes to work for my situation? After the user logs in their username is then placed in cell AD5 of the master index page so ideally, I would like your code to recognize when that username is within a named range called "Commander_Login". If the username is within that named range then your code is then activated and the workbook will change to read-only.

I noticed something, when the workbook is changed to read only, I am still able to enter data within the sheets but when I go to save or exit a message box comes up saying that the file needs to be renamed in order to save. Is there a way to somehow not allow that at all?

I really appreciate your help!

Dave

 

[Jaafar Tribak]

Dave,

This is a more specific code that checks if the name in cell D5 is contained within the named range and if so makes the current workbook RedOnly.

Place this a Standard Module and run the Test Macro :

Function MakeWorkbookReadOnly(ByVal Wb As Workbook) As Boolean

    With Wb
        If Not .ReadOnly Then
            .Saved = True
            .ChangeFileAccess xlReadOnly
        End If
        MakeWorkbookReadOnly = .ReadOnly
    End With

End Function

Function IsNameWithinRange(ByVal TheName As String, ByVal TheRange As Range) As Boolean

    On Error Resume Next
    IsNameWithinRange = CBool(Application.WorksheetFunction.Match(TheName, TheRange, 0))
    
End Function


Sub Test()

    If IsNameWithinRange(Range("'MASTER INDEX'!AD5"), Range("Commander_Login")) Then
        If MakeWorkbookReadOnly(ThisWorkbook) Then
            MsgBox ThisWorkbook.Name & " is now ReadOnly"
        End If
    End If
    
End Sub

 

[Jaafar Tribak]

I noticed something, when the workbook is changed to read only, I am still able to enter data within the sheets but when I go to save or exit a message box comes up saying that the file needs to be renamed in order to save. Is there a way to somehow not allow that at all?

ReadOnly means that the user can still work\edit the worksbook and worksheets but can only save the changes to a ReadOnly copy of the original workbook (the original workbook stays intact and changes to it can only be saved by the users who have Read-Write permissions)

 

[dsheard2015]

Jaafar,

Your new code works great too as long as I run the code from visual basics. When I open the workbook and login using a user from the named range it does not activate the code and go to read only. How would I do that?

Thanks for explaining how the read only feature works. I tried to save the read only copy and unfortunately I was able to rename the file with the same name as the original which would essentially replace the original, That would not be a good thing to happen, lol. Do you know of a better way of accomplishing what I need? Maybe changing to read only mode isn't the best thing? If that is the only way than it will have to be good.

Thanks again,

Dave

 

[Jaafar Tribak]

Your new code works great too as long as I run the code from visual basics. When I open the workbook and login using a user from the named range it does not activate the code and go to read only. How would I do that?

That Test macro should be called right after the code you already have that retrieves the username.

Thanks for explaining how the read only feature works. I tried to save the read only copy and unfortunately I was able to rename the file with the same name as the original which would essentially replace the original, That would not be a good thing to happen, lol. Do you know of a better way of accomplishing what I need? Maybe changing to read only mode isn't the best thing? If that is the only way than it will have to be good.

Don't know ... Maybe instead of making the file readonly, you could protect the workbook and all its worksheets but then the users will not be allowed to do anything apart from viewing.

 

[dsheard2015]

I have a meeting to get to but will get back on this in about an hour or so.

Thanks for all your help Jaafar!

Dave