Show/Hide Rows by user input

diggerdidoodar

Board Regular
Joined
May 20, 2002
Messages
59
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top