VBA - Code to Make Workbook READ-ONLY vs Edit based on drop down selection

[Rana Gray]

Hello Again!

I found an older thread with a similar question but I couldn't figure out how to apply it to my situation.

On my [Sheet1] I have a drop down box setup that has 2 options (Inquiry/Update) what I would like to do is write a script that changes the workbook from read only to editable based on the relevant selection...is this doable? I'm fine if the read-only starts from [Sheet2] onwards if that makes more logical sense.

As always thanks a bunch in advance!!!

 

[Jaafar Tribak]

Try placing this code in the module behind the worksheet where the validation cell is located :
The code assumes the validation cell is A1... chnage this cell address via the constant declared at the top of the module.

Option Explicit

Private Const VALIDATION_CELL_ADDRESS = "A1" '<== change Cell addrss to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range(VALIDATION_CELL_ADDRESS).Address Then
        If Target = "Inquiry" Then
            ChangeWorkbookAccess ThisWorkbook, xlReadOnly
        Else
            ChangeWorkbookAccess ThisWorkbook, xlReadWrite
        End If
    End If
End Sub

Private Sub ChangeWorkbookAccess( _
    ByVal Wb As Workbook, _
    ByVal FileAccess As XlFileAccess _
)
    With Wb
        .Saved = True
        On Error Resume Next
            .ChangeFileAccess FileAccess
        On Error GoTo 0
    End With

End Sub

Note that any unsaved changes will be lost !

 

[Rana Gray]

Try placing this code in the module behind the worksheet where the validation cell is located :
The code assumes the validation cell is A1... chnage this cell address via the constant declared at the top of the module.

Option Explicit

Private Const VALIDATION_CELL_ADDRESS = "A1" '<== change Cell addrss to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range(VALIDATION_CELL_ADDRESS).Address Then
        If Target = "Inquiry" Then
            ChangeWorkbookAccess ThisWorkbook, xlReadOnly
        Else
            ChangeWorkbookAccess ThisWorkbook, xlReadWrite
        End If
    End If
End Sub

Private Sub ChangeWorkbookAccess( _
    ByVal Wb As Workbook, _
    ByVal FileAccess As XlFileAccess _
)
    With Wb
        .Saved = True
        On Error Resume Next
            .ChangeFileAccess FileAccess
        On Error GoTo 0
    End With

End Sub

Note that any unsaved changes will be lost !

Thanks so much it did work but not quite what I had in mind - which is completely my fault I guess I was going for more locked worksheets when inquiry is selected. Even in 'Inquiry' mode I was still able to make changes.

 

[Jaafar Tribak]

Thanks so much it did work but not quite what I had in mind - which is completely my fault I guess I was going for more locked worksheets when inquiry is selected. Even in 'Inquiry' mode I was still able to make changes.

Hi Rana,
Well, in that case, it is easier.... Do you want this to be applied to just the worksheet where the drop down list is located or to all worksheets ?

 

[Rana Gray]

Hi Rana,
Well, in that case, it is easier.... Do you want this to be applied to just the worksheet where the drop down list is located or to all worksheets ?

I'm looking to lock all worksheets - let's say from [sheet2] onwards when "inquiry" mode is selected, does that make sense?