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?
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?
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.