How do I lock or unlock sheets based on values or data in other sheets

0

i am trying to Lock 15 other sheets in a workbook, so that info cant be filled out in these 15 sheets until the info on the first sheet is filled out.  How would i go about doing that?

Answer
Discuss

Answers

0

It is possible to apply worksheet protection programmatically. Google for "VBA to protect worksheet" or record a macro. However, a better solution might be to simply hide the sheets until the first sheet has been filled.

Worksheets("MySheetName").Visible = xlSheetVeryHidden

Using the xlSheetVeryHidden constant will prevfent the hidden sheet from being listed ias "hidden". Therefore the user won't be able to unhide the sheet from the worksheet level. To unhide it access to the VBA project is required, or you can unhide it using code. The alternative to xlSheetVeryHidden is xlSheetHidden (and xlSheetVisible to unhide it).

The attached workbook has 3 sheets. The code below is in the 'ThisWorkbook' module. It runs automatically when you open the workbook.

Option Explicit
Private Sub Workbook_Open()
    ' 20 Jan 2018
    
    Sheet1.Activate
    Sheet2.Visible = xlSheetVeryHidden
    Sheet3.Visible = xlSheetVeryHidden
End Sub
Please observe that the worksheets are addressed by their code names. In the VBA Project Explorer (in the VB Editor window) you see the sheets listed like "Sheet1 (Sheet1)". The first name is the code name, which I use in my code. The name in brackets is the tab name which you can change on the worksheet (or in the properties). Point is, the code name can only be changed in the properties and it will not change, regardless which name you give to the tab.

The first two sheets have a "Next" button, each is assigned a macro (CheckSheet1 and CheckSheet2) you will find in the 'CheckAndShow' code module.

Option Explicit
Sub CheckSheet1()
    ' 20 Jan 2018
    
    Dim CheckRange() As Variant
    Dim Desc As String
    
    ' match Checkranges to Descriptions
    CheckRange = Array("B3", "B5", "B7")
    ' use a vertical bar | to separate items
    Desc = "Name|Age|Gender"
    
    If CheckSheet(Sheet1, CheckRange, Desc) Then
        With Sheet2
            .Visible = xlSheetVisible
            .Activate
            .Range("C3").Select
        End With
    End If
End Sub
Sub CheckSheet2()
    ' 20 Jan 2018
    
    Dim CheckRange() As Variant
    Dim Desc As String
    
    ' match Checkranges to Descriptions
    CheckRange = Array("C3", "C6", "C9")
    ' use a vertical bar | to separate items
    Desc = "Qualification|Work experience|Expected pay"
    If CheckSheet(Sheet2, CheckRange, Desc) Then
        With Sheet3
            .Visible = xlSheetVisible
            .Activate
            .Range("D3").Select
        End With
    End If
End Sub
Both macros call the function 'CheckSheet'. You will find it at the bottom of the 'CheckAndShow' code module.
Private Function CheckSheet(Ws As Worksheet, _
                            CheckRange() As Variant, _
                            Desc As String) As Boolean
    ' 20 Jan 2018
    ' return Not True if a fault was found
    
    Dim Itm() As String
    Dim i As Long
    
    Itm = Split(Desc, "|")
    For i = 0 To UBound(CheckRange)
        With Ws.Range(CheckRange(i))
            If .Value = "" Then
                MsgBox "Please enter your " & Itm(i) & vbCr & _
                       "in cell " & CheckRange(i) & ".", _
                       vbExclamation, "Missing entry"
                .Select
                Exit For
            End If
        End With
    Next i
    CheckSheet = (i > UBound(CheckRange))
End Function
The function checks each of the cells specified in the CheckRange array. If an entry is missing it gives an error message modulated according to the Desc string (converted into an array in the function), selects the faulty cell and returns FALSE. If all entries are found the function returns TRUE, and this causes the next sheet to be made visible, activated and the first cell to fill being selected.

The attached workbook contains a fully functioning example of the above.

Discuss

Discussion

could you show an example of how to use this... i want it so that the second sheet wont unlock until the first sheet is filled out, and then the third sheet wont unlock until the second is unlocked... so on and so on
bdalfrey23 Jan 19, '18 at 10:15 am
I have expanded my reply to include the example you requested.
Variatus (rep: 823) Jan 19, '18 at 10:23 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login