Checking existence of sheets and hiding / un-hiding based on cell values

Question

I am very much an amateur at this!! Currently I have a worksheet which will have a list of users going down and then a list of sheet names / numbers going across

The overall expectation is that if the user has a 1 in the cell under the sheet then the sheet is visible, otherwise the sheet should be very hidden

This code is in module 1 although not sure if it should be at workbook level instead.

I declare the following outside of the functions so they can be called within each;

Dim lRow As Long, wsStatus As Boolean, shtloc As Integer

Then this is my main function (which maybe should be a sub at workbook level, not sure)

    Function CheckSheetPermission()

Sheet9.Visible = xlSheetVisible
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Welcome" Then ws.Visible = xlSheetVeryHidden
Next ws
Sheet12.Visible = xlSheetVisible
Sheet12.Activate
With ActiveSheet
Dim sht As Worksheet
Call GetRowNum(Range("A3:A200"), LCase(Environ("UserName")))
    For Each Cell In Range("B" & lRow & ":GS" & lRow)
    If Abs(Cell.Value) = "1" Then
        shtloc = Cell(2, ActiveCell.Column).Value
        Call wsExists(Sheets(shtloc))
        If wsStatus = False Then
            Sheets(shtloc).Visible = xlSheetVisible
        End If
    Else
        shtloc = Cell(2, ActiveCell.Column).Value
         Call wsExists(Sheets(shtloc))
        If wsStatus = False Then
            Sheets(shtloc).Visible = xlSheetVeryHidden
        End If
    End If
    Next
End With
End Function

This looks at the sheet below, it is initially meant to make sure both welcome and sheet 1 are temp visible until the code has executed which then sets the correct visibility for all sheets (I assume the sheet has to be visible for the code to read the cells)

image of worksheet

Get row number simply looks up the user and gets the row number

Call GetRowNum(Range("A3:A200"), LCase(Environ("UserName")))

And that function code is

Function GetRowNum(rng As Range, user As String)


    On Error Resume Next
    lRow = Application.WorksheetFunction.Match(user, rng, 0) + 2
    On Error GoTo 0


End Function

Where it seems to be going wrong currently is when I call the next function;

Call wsExists(Sheets(shtloc))

I get the subscript out of range error. Which might be because of the variable before not being set properly or the function itself I am calling but I am not sure where best to start amending code and checking results to further isolate the issue

Limited error handling at this stage as trying to trap errors. if I add error handling to the main function it just passes through and no sheets are hidden or visible other than those set at the start (sheet control and welcome)

Function wsExists(wsSheet As Worksheet)
On Error Resume Next
On Error GoTo 0
If Not wsSheet Is Nothing Then
wsStatus = "True"
Else
wsStatus = "False"
End If

End Function

Please let me know if you need more information.

Simply what I am trying to achieve is the following steps;

1. Check which sheets should be visible to the user opening the file through a loop
2. Loop through each of the sheet numbers if they exist using the sheet number variable in conjunction with the 0/1 value under the row next to the user name and set sheet visibility(1 = allowed.0 = not allowed)

I will admit that perhaps this may be a lot better scripted for those who have better understanding

###EDIT

Final code after suggested improvements and some additional changes after I found some extra bugs

Thisworkbook

Private Sub Workbook_Open()

Dim ws As Worksheet
Dim shtloc As String
Dim c As Long
Dim lRow2 As Long
Sheet9.Visible = xlSheetVisible

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Welcome" Then ws.Visible = xlSheetVeryHidden
Next ws
Sheet12.Visible = xlSheetVisible
Sheet12.Activate
With Sheet12
    lRow2 = Module2.lRow2(Range("A3:A5"), LCase(Environ("UserName")))


        For c = 2 To ActiveWorkbook.Worksheets.Count + 1
        shtloc = Cells(1, c).Value2
             With Cells(lRow2, c)
             perm = Cells(lRow2, c).Value2
                Select Case perm
                    Case ("V")
                    'Visible
                        Sheets(shtloc).Visible = xlSheetVisible
                    Case ("P")
                    'visible protected
                        Sheets(shtloc).Visible = xlSheetVeryHidden
                        Sheets(shtloc).Protect Password:="*********"
                    Case ("D")
                    'Access denied
                        Sheets(shtloc).Visible = xlSheetVeryHidden
                    End Select
             End With
        Next c

End With

End Sub

Module

Function lRow2(rng As Range, user As String)

    On Error Resume Next
    With ActiveSheet
    lRow2 = Application.WorksheetFunction.Match(user, rng, 0)

    lRow2 = lRow2 + 2

    End With
End Function

I no longer check if the sheets exist either as I do a count of sheets and don't carry on the next loop to the blank cells which await new sheet creations to be populated...speeding up the code as well

Answer 1

There are a few errors in your code. One things that I would do to avoid confusion would be to use a different iterator within your for loop. Instead of

For Each Cell In Range("B" & lRow & ":GS" & lRow)

I would use

For Each currentCell In Range("B" & lRow & ":GS" & lRow)

This would avoid the first error that I noted:

shtloc = Cell(2, ActiveCell.Column).Value

This line will not return what you want. First, you need an "s" at the end of "Cell". Second, your sheets names are on row 1, third, your code is not clear on which cell is currently activated, this could return strange behaviours. Try instead to replace it by:

shtloc = Cells(1, currentCell.Column).Value

Now your main error is that you try to check if the sheet exists by triggering an error. This generates your out of range error.

Instead you should use a different function to check if the read sheet name exists or not. This answer provides exactly what you want

https://stackoverflow.com/a/6040454/10645669

Function sheetExists(sheetToFind As String) As Boolean sheetExists = False For Each sheet In Worksheets If sheetToFind = sheet.name Then sheetExists = True Exit Function End If Next sheet End Function

---

On a different note, it seems to me that you are using global variables because you are not properly returning values out of your functions. For instance

Function wsExists(wsSheet As Worksheet)
On Error Resume Next
On Error GoTo 0
If Not wsSheet Is Nothing Then
wsStatus = "True"
Else
wsStatus = "False"
End If

End Function

Can be written:

Function wsExists(wsSheet As Worksheet) as boolean
On Error Resume Next
On Error GoTo 0
If Not wsSheet Is Nothing Then
wsExists = "True"
Else
wsExists = "False"
End If

End Function

This allows you to use the function directly in your code like so:

If wsExists(Sheets(shtloc)) = False Then

No more global variable, and no more use of the keyword call.

Also, as a rule of thumb, if the written code does not return anything, then it should be a "sub". These can be called without the keyword call by just stating the arguments without brackets. Example:

subCalled argument1, argument2, argumentN