Odtnhj

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

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

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