• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Subscript out of range error

Thomas Kuriakose

Active Member
Respected Sirs,

Good day to you, I need your guidance on a task where we have multiple users working on a workbook.

We would like to give access for users to work on sheets as be below user list table. The "x" should have the sheet visible and * should not have the sheet visible. I am getting an error subscript out of range at line highlighted in the below clip. This is triggered on workbook open.
UsernamePasswordSheet1Sheet2Sheet3Sheet4Sheet5Sheet6Sheet7Sheet8Sheet9Sheet10Sheet11Sheet12Sheet13Sheet14
ANNA1Xx***********
JENA2xx************
CATA3x***x*********
LOYA4x*********xx**
SADA5x**x**********
MENMENxxxxxxxxxxxxxx

65487

The
Code:
Private Sub Workbook_Open()
Dim Sh As Worksheet
Dim UserName As String
Dim Password As String
Dim ThisCell As Range
Dim c As Long
 For Each Sh In ThisWorkbook.Worksheets
 If Sh.Name <> "Welcome" Then
 Sh.Visible = xlSheetVeryHidden
 End If
 Next Sh
 UserName = InputBox("Please enter your user name.")
 Password = InputBox("Please enter password.")
 For Each ThisCell In Sheets("User List").Range("A2:A" & Sheets("User List").Range("A65536").End(xlUp).Row)
 If UCase(ThisCell.Value) = UCase(UserName) And UCase(ThisCell.Offset(, 1).Value) = UCase(Password) Then
 MsgBox "Access Granted"
 For c = 2 To 14
 'This is the number of sheets from C1 to E1
 If ThisCell.Offset(, c).Value <> "" Then
 Sheets(Sheets("User List").Cells(1, c + 1).Value).Visible = xlSheetVisible
 End If
 Next c
 Exit Sub
 End If
 Next ThisCell
 MsgBox "Access Denied"
 ThisWorkbook.Close
End Sub

The workbook has also got a worksheet change event for Index tab (A2) which on selection of data validation also hides and unhide respective sheets. This selection is given to get pictures of respective department on drop down selection from Index tab (A2) as well as calculations in other tabs.

I feel the workbook open and the worksheet change are duplicated, but have different purpose.

Kindly guide on how we can use both the codes either with a user form to get results of access as well as hiding and in hiding sheets. Or what would be the correct way to go about this.

Thank you very much for your guidance,

with regards,
thomas
 

Attachments

  • SheetHide on Selection.xlsm
    46.4 KB · Views: 5
Hi Thomas !​
Your issue comes from your users list table as the sheet name "Sheet1" does not exist in the Sheets collection !​
Instead of "Sheet1" it must be "Index" … So you have to fit your table according to the sheets names …​
 
Respected Sir,

Thank you so much for this guidance and your support. This worked perfectly after changing the sheet names.

Sir, sorry for asking, if you have time kindly check the possibility to avoid both the worksheet change event for hiding unhanding sheets and the workbook open event of access to users per sheet.

Index sheet has cell A2 with drop down selection and it triggers the worksheet change event, but at the same time this entry is used for changing picture in C3 (macro removed) due to size of file and also this cell reference triggers other calculations in other sheets.

The objective is to ensure the users are not able to view the sheets they should not. But if we have the worksheet change even from A2, this will also enable users to see other sheets.

Thank you very much for your kind support always.

with regards,
thomas
 

Attachments

  • SheetHide on Selection.xlsm
    46.9 KB · Views: 6
For the Change event why don't you allocate the drop down with only the visible sheets ?​
As the easy way is to remove the drop down and the Change event code …​
The revamped Open event which should not crash even if a name doesn't exist in the Worksheets collection :​
Code:
Private Sub Workbook_Open()
       Const P = vbLf & vbLf & " Please enter your "
         Dim Sh As Worksheet, V, R, C%
    For Each Sh In Worksheets
        Sh.Visible = IIf(Sh.CodeName = "Sheet13", xlSheetVisible, xlSheetVeryHidden)
    Next
        V = Sheet14.UsedRange.Value2
        R = Application.Match(InputBox(P & "user name :"), Application.Index(V, , 1), 0)
        If IsNumeric(R) Then If UCase(InputBox(P & "password :")) <> UCase(V(R, 2)) Then R = CVErr(1)
        If IsError(R) Then MsgBox "Access Denied !": Me.Close False: Exit Sub
    For C = 3 To UBound(V, 2)
        If V(R, C) > "" Then If Evaluate("ISREF('" & V(1, C) & "'!A1)") Then Sheets(V(1, C)).Visible = xlSheetVisible
    Next
        If Sheet1.Visible = xlSheetVisible Then Sheet1.Activate
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Respected Sir,

The revised code worked perfectly. Thank you for your guidance and support on this.

Sir, If the drop down allocation for visible sheets needs to be used how can the change event code be changed to ensure the drop down has only visible sheets. Ca n the username be used as a trigger to get the drop down list.

Thank you very much once again,

Much appreciated.

with regards,
thomas
 
Clear first all code within the Sheet1 (Index) worksheet module as there is no relation with what you expect for !​
As only the granted tabs are visible, do you really need this drop down, a bit superfluous, no ?​
 
Respected Sir,

Thank you very much once again,

I cleared all the codes within Sheet1 module. Yes agreed the drop down, is duplicated and I will use it differently for the picture selection and for calculation of other sheets without the visibility requirement.

Much appreciated.

with regards,
thomas
 
Respected Sirs,

Sorry to come back again on this. There is a new exception require now.

How can we exclude Usernames MEN and SAD from the username a password login routine. These two users should be able to access the file without a login and password prompt and access all tabs.

Thank you very much for your guidance and support always,

with regards,
thomas
 
I MEN and SAD use there own computer you can use Environ("username")
In the ThisWorkbook section
On top of your code, just under Dim c As Long add this line of code.
Code:
If Environ("username") = "MEN" Or Environ("username") = "SAD" Then GoTo nolog
And add these lines of code at the end just under
ThisWorkbook.Close.
Code:
nolog:
For Each ws In ThisWorkbook.Worksheets
    ws.Visible = xlSheetVisible
Next ws
Important, they have to use there own computer and the Environ("username") must be correct.
To check the correct Environ("username") for MEN and SAD use this macro on their computer.
Code:
Sub belle()
    MsgBox Environ("username")
End Sub
 
How can we exclude Usernames MEN and SAD from the username a password login routine.
Code:
Private Sub Workbook_Open()
           Const P = vbLf & vbLf & " Please enter your "
             Dim Sh As Worksheet, V, R, C%
    If InStr("¤MEN¤SAD¤", "¤" & Environ("USERNAME") & "¤") Then
        For Each Sh In Worksheets:  Sh.Visible = xlSheetVisible:  Next
    Else
        For Each Sh In Worksheets
            Sh.Visible = IIf(Sh.CodeName = "Sheet13", xlSheetVisible, xlSheetVeryHidden)
        Next
            V = Sheet14.UsedRange.Value2
            R = Application.Match(InputBox(P & "user name :"), Application.Index(V, , 1), 0)
            If IsNumeric(R) Then If UCase(InputBox(P & "password :")) <> UCase(V(R, 2)) Then R = CVErr(1)
            If IsError(R) Then MsgBox "Access Denied !": Me.Close False: Exit Sub
        For C = 3 To UBound(V, 2)
            If V(R, C) > "" Then If Evaluate("ISREF('" & V(1, C) & "'!A1)") Then Sheets(V(1, C)).Visible = xlSheetVisible
        Next
    End If
        If Sheet1.Visible = xlSheetVisible Then Sheet1.Activate
End Sub
You may Like it !​
 
Respected Sir,

Thank you for all the support on this automation and sorry for bothering again on this. I need your guidance and support again on this.

All the codes provided are working perfectly, but the user needs to have the work sheet event change based on drop down selection to activate along with the login password.

The reason being - There are six users who need password for login and who should only see the respective tabs assigned to them. There are another set of six users who need to be given access to all tabs.

You had suggested in the second post : "For the Change event why don't you allocate the drop down with only the visible sheets ?" How can we alter the drop down selection as per the visible sheets, this will work if done. Kindly suggest and guide on the correct way to get this working. Or would you suggest to use username for the Cases in worksheet change event.

I have edited the file with picture selection on drop down selection in Index tab A2, and the currency in A8, these are linked to all the tabs, one example is in tab MEN (A1:B2).

One last query if the work sheet change event is activated, will the user be able to right click and unhide a tab which is not of a different department.

Thank you very much for your kind support and guidance always,

Very much appreciated.

with regards,
thomas
 

Attachments

  • SheetHide on Selection.xlsm
    137.1 KB · Views: 2
will the user be able to right click and unhide a tab which is not of a different department
Yes as yet stated until you remove all the irrelevant codelines from the Change event which cancel all the job done by the Open event ‼​
Check yourself after the workbook opening for Ann for example, right click on the tab and see you can't unhide any sheet.​
But once you use the drop down you can see the disaster from this Change event …​
So according to the Open event, just respecting the Logic, remove any codeline which contains Visible
as again it's so weird to hide / unhide any sheet as all the job is yet done via the Open event !​
In the Open event, each time a sheet is visible for an user add it to a string (comma as delimiter)
and before the event ends delete the cell data validation and recreate it using the string for the sheets list​
like when you operate manually (activate before the Macro Recorder, so easy) …​
 
Respected Sir,

Thank you for this guidance and support,

So according to the Open event, just respecting the Logic, remove any codeline which contains Visible
as again it's so weird to hide / unhide any sheet as all the job is yet done via the Open event !

In the Open event, each time a sheet is visible for an user add it to a string (comma as delimiter)and before the event ends delete the cell data validation and recreate it using the string for the sheets listlike when you operate manually (activate before the Macro Recorder, so easy)
[/QUOTE]

Sir, I could not follow on how to add and edit the code as per the above. Kindly help on this.

Thank you very much.

with regards,
thomas
 
Respected Sir,

I found a code which gives all the sheet tabs in data validation in cell A2 of Index tab, but do not know how to have the visible option working with this.

Code:
Private Sub Worksheet_Activate()
If ActiveCell.Address = "$A$2" Then
    ActiveCell.Offset(-1).Select
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Long
If Target.Address <> "$A$2" Or Target.Cells.Count > 1 Then Exit Sub
For x = 2 To Worksheets.Count
    Cells(x, "Z") = Sheets(x).Name
Next
With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$Z$2:$Z$" & x - 1
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub

The hide and unhide does not work, when the above code is used.

Thank you for your guidance and support,

with regards,
thomas
 

Attachments

  • SheetHide on Selection.xlsm
    145.5 KB · Views: 3
The hide and unhide does not work, when the above code is used.
Your bad as you are still following an irrelevant logic !​
Last try : as I yet wrote, the Change event DOES NOT NEED to hide / unhide any sheet 'cause it's yet done at the opening​
so again delete any codeline containing Visible in this Change event of your post #12 attachment, last one is crappiest …​
 
Respected Sir,

My blunders, and apologies, yes now I removed all the code lines containing visible in the change event.

Thank you for this guidance.

The new change event code looks like this.

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2")) Is Nothing Then
    Application.ScreenUpdating = False

    Select Case Target.Value
        Case "JEN"
        Case "ANN"
        Case "SAD"
        Case "CAT"
        Case "MEN"
        Case Else
    
    End Select
    Application.ScreenUpdating = True
End If
End Sub
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "$B$20" Then

    
Else
    If Target.Range.Address = "$E$20" Then

    End If
    End If
End Sub

Last request:
If the six users who have access to all tabs, how can we make a navigation on the tabs based on a selection if possible.

Thank you for all the patience and guidance on this project,

with regards,
thomas
 

Attachments

  • SheetHide on Selection_V5.xlsm
    136.5 KB · Views: 3
Respected Sir,

Thank you for your guidance, I thought about the logic once again and it seems this will not work to have a drop down selection as well as password login at the same time working, as was the case initially.

We needed to check the possibility of restricting user through the login and password as well as use the drop down selection to navigate through the tabs (hide/unhide the respective tabs) based on the department selected and if a user selected the drop down of a different department to give a message " access denied".

The six users who have access to all tabs have now to select each sheet to check data without filter of the department. The drop down selection gave an option to select one department and only the corresponding sheets.

Thank you so much for all the time and guidance on this,

very much appreciated,

with regards,
thomas
 
Respected Sir,

Correct sir, what I meant is on the departments were, MEN, ANN, CAT, SAD and LOY (Worksheets) and the change event would trigger the corresponding sheets for each department for example department MEN, which included MEN, data, data list and Index to be visible.

Thank you very much once again,

with regards,
thomas
 
So obviously in the sheet 'Index" you just need a Change event containing a single codeline to just Activate the selected sheet …​
For the drop down list I yet explained in my previous posts - it's just about reading - but first, how do you have created this drop down ?​
 
Respected Sir,

sorry for the late reply, not well.

The drop down currently was created using data validation with reference to input to values in data list tab (J11:J16), as these drop drowns also change the image (K11:K16) named range (Dimage) in index and other tabs.

The activation of selected tabs is based on the department selected, so user can input data in their respective department tab and it shows only the other tabs which have calculations related to the department tab.

Thank you very much once again,

with regards,
thomas
 
So why wasting time to find a code on web which again does not well fit your need like in your post #15​
as the easy way is to just activate the Macro Recorder and operate manually like I advised in post #13 :​
In the Open event, each time a sheet is visible for an user add it to a string (comma as delimiter)
and before the event ends delete the cell data validation and recreate it using the string for the sheets list
like when you operate manually (activate before the Macro Recorder, so easy)
So as a beginner starter according to the Macro Recorder - done by a child who I gave it yesterday as a trial - and following this post #13 :​
Code:
Private Sub Workbook_Open()
           Const P = vbLf & vbLf & " Please enter your "
             Dim Ws As Worksheet, V, R, C%, F$
    If InStr("¤MEN¤SAD¤", "¤" & Environ("USERNAME") & "¤") Then
        For Each Ws In Worksheets:  Ws.Visible = xlSheetVisible:  Next
    Else
        For Each Ws In Worksheets
            Ws.Visible = IIf(Ws.CodeName = "Sheet13", xlSheetVisible, xlSheetVeryHidden)
        Next
            V = Sheet14.UsedRange.Value2
            R = Application.Match(InputBox(P & "user name :"), Application.Index(V, , 1), 0)
            If IsNumeric(R) Then If UCase(InputBox(P & "password :")) <> UCase(V(R, 2)) Then R = CVErr(1)
            If IsError(R) Then MsgBox "Access Denied !": Me.Close False: Exit Sub
        For C = 3 To UBound(V, 2)
            If V(R, C) > "" Then If Evaluate("ISREF('" & V(1, C) & "'!A1)") Then Sheets(V(1, C)).Visible = xlSheetVisible
        Next
    End If
    If Sheet1.Visible = xlSheetVisible Then
        For Each Ws In Worksheets
            If Ws.Visible = xlSheetVisible Then F = F & Ws.Name & ","
        Next
            With Sheet1.[A2].Validation:  .Delete:  .Add xlValidateList, xlValidAlertInformation, , F:  End With
            Sheet1.Activate
    End If
End Sub
For the Index Change event your starter is​
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then If Not IsEmpty(Target) Then Sheets(Target.Text).Activate
End Sub
As a starter, you may have to amend it to fit all the parts you didn't well technically explain,​
as after all, we are not on a mind readers forum …​
 
Back
Top