Show/Hide Rows by user input

[diggerdidoodar]

Hi probably been asked 100,s of time but can't see what I'm looking for.
Can you open a worksheet in excel & only show rows relevant to whats types into an "InputBOX" ie a number code. Also ensure nobody can view rows that are not relevant to themselves.

 

[mumps]

If you enter the number 5 in the input box, you would want to show only row 5. Is this correct? By "open a worksheet" do you mean open a workbook (file) or activate a worksheet? You will have to give more details on what you mean by:

Also ensure nobody can view rows that are not relevant to themselves.

 

[diggerdidoodar]

Hi sorry for being a bit vague, its a security thing really so i only want people to see their own data.
IE if I allocated someone a security code of 1234 they could only see certain rows within the worksheet & no other rows.
If I then allocated someone a security code of 5678 they could only see their specific rows within the worksheet & no other rows, etc etc.
Code needs to be imputed into a cell or "inputBox"!
Hope that makes sense.

 

[mumps]

Do you want the InputBox to appear when you open the file or when you activate the sheet? What is the name of the sheet involved? You would need to have another sheet, let's name it "Codes", which would contain the security codes for each person and their corresponding rows which they are able to view. This "Codes" sheet would be hidden so that the codes would not be visible to the user.

 

[diggerdidoodar]

Hi, thanks for the reply, name of the current sheet is "Leave Tracker"
"Do you want the InputBox to appear when you open the file or when you activate the sheet" --- Whatever works best.

 

[mumps]

Start by creating a sheets named "Codes". In column A enter all your codes and in column B enter the corresponding rows you want to be visible separated by commas (no spaces)like so:

Code Rows
1234 3,6,9,12
5678 2,4,6,8

Hide this sheet so that it isn't visible. Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim response As String, code As Range, rows As Variant, i As Long
    Dim LastRow As Long
    LastRow = Sheets("Leave Tracker").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Leave Tracker").rows("2:" & LastRow).Hidden = True
    response = InputBox("Please enter your code.")
    If response = "" Then
        MsgBox ("You have not entered a code.")
        ActiveWorkbook.Close False
    ElseIf response <> "" Then
        Set code = Sheets("Codes").Range("A:A").Find(response, LookIn:=xlValues, lookat:=xlWhole)
        If Not code Is Nothing Then
            Sheets("Leave Tracker").rows(1).Hidden = False
            rows = Split(code.Offset(0, 1).Value, ",")
            For i = LBound(rows) To UBound(rows)
                Sheets("Leave Tracker").rows(rows(i)).Hidden = False
            Next i
        Else
            MsgBox ("Invalid code.")
            ActiveWorkbook.Close False
        End If
    End If
    Application.ScreenUpdating = True
End Sub

Save the workbook as a macro-enabled file and close it. When you re-open it, you will be prompted to enter a code. If you do not enter a code or enter an invalid code, the workbook will close.

 

[diggerdidoodar]

Mumps - That is tremendous. If I can be cheeky, how do I only allow the user to view their own rows & not just unhide rows to see the rest.
Also they need to be able to edit their own rows & nobody elses rows. Great so far though, thank you.

 

[mumps]

Try this macro. In order to do what you want, I had to protect the sheet. The password (in red) is "mypassword". Change it to a password of your choice.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim response As String, code As Range, rows As Variant, i As Long
    Dim LastRow As Long
    LastRow = Sheets("Leave Tracker").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Sheets("Leave Tracker")
        .Unprotect Password:="[COLOR="#FF0000"]mypassword[/COLOR]"
        .rows("2:" & LastRow).Hidden = True
        .Cells.Locked = True
    End With
    response = InputBox("Please enter your code.")
    If response = "" Then
        MsgBox ("You have not entered a code.")
        With Sheets("Leave Tracker")
            .Protect Password:="[COLOR="#FF0000"]mypassword[/COLOR]"
            .EnableSelection = xlUnlockedCells
        End With
        ActiveWorkbook.Close False
    ElseIf response <> "" Then
        Set code = Sheets("Codes").Range("A:A").Find(response, LookIn:=xlValues, lookat:=xlWhole)
        If Not code Is Nothing Then
            Sheets("Leave Tracker").rows(1).Hidden = False
            rows = Split(code.Offset(0, 1).Value, ",")
            For i = LBound(rows) To UBound(rows)
                With Sheets("Leave Tracker").rows(rows(i))
                    .Hidden = False
                    .Cells.Locked = False
                End With
            Next i
        Else
            MsgBox ("Invalid code.")
            With Sheets("Leave Tracker")
                .Protect Password:="[COLOR="#FF0000"]mypassword[/COLOR]"
                .EnableSelection = xlUnlockedCells
            End With
            ActiveWorkbook.Close False
        End If
    End If
    With Sheets("Leave Tracker")
        .Protect Password:="[COLOR="#FF0000"]mypassword[/COLOR]"
        .EnableSelection = xlUnlockedCells
    End With
    Application.ScreenUpdating = True
End Sub

 

[diggerdidoodar]

Thanks again Mumps, get an error message now Error 1004, "Unable to set the locked Property of the Range class" fails here:-
ElseIf response <> "" Then
Set code = Sheets("Codes").Range("A:A").Find(response, LookIn:=xlValues, lookat:=xlWhole)
If Not code Is Nothing Then
Sheets("Leave Tracker").rows(1).Hidden = False
rows = Split(code.Offset(0, 1).Value, ",")
For i = LBound(rows) To UBound(rows)
With Sheets("Leave Tracker").rows(rows(i))
.Hidden = False
****** .Cells.Locked = False *******
End With
Next i
Else

 

[mumps]

I'm not sure why you are getting the error. I tried the macro on a dummy file and it worked properly. Click here to download the sample file and give it a try.