Hi and welcome to forum.
Yes, it is possible. However, you need to know that this requires VBA code.
When opening a workbook containing VBA code (macros), users are prompted to make a choice to enable the macros ( or not ). If they choose to not enable macros then all code is defeated. In short, Excel is not a secure platform.
I suggest you take this approach:
Use the Custom Views option (View tab > Custom Views) to set up the various views.
First, with all rows visible, create a view and name something like "View All".
Now, say you want to set up a view for "Joe", hide all of the rows you don't want Joe to see then name this view as "Joe's View" (or whatever you like).
Unhide all rows, then create another view for "Alexis" using the same process.
After creating all of the various views you need, make sure all rows are visible then hide all rows and name this view as "All Hidden". You should probably leave at least one row visible as you might want to use a drop down list for users to select from (you need a method to allow them to select their rows of data).
Say we leave row-1 visible and use A1 as the drop down. Cell A1 must be unlocked (Cell Format > Properties, uncheck "Locked")
Once you've created all of the views, record a macro stepping through the following process:
Start the Macro Recorder
Select the View tab on the Ribbon
Click the Custom Views button and select any view
Stop the Macro Recorder
You now have the basic code the call up a view. We jsut need to modify to use the drop down selection.
We will also need to make sure that the Hide All view is the current view when opening the workbook so that uses do not see anything from a previous view. We must the workbook_open event for this.
Here is the basic idea and a sample workbook.
Code used when opening the workbook.
Private Sub Workbook_Open()
With Sheet1 'sheet code name, change as needed
ActiveWorkbook.CustomViews("Hide All").Show
End With
End Sub
This code must go into the worksheet module.
This is basic code - no real error handling.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg As String, strPassword As String, strView As String
msg = "Please enter your password"
On Error GoTo End_it
Sheet1.Unprotect "Secret"
If Target.Address = "$A$1" Then 'user is in the drop down selection cell
If Target <> vbNullString Then 'make sure there is a valid selection (not empty cell)
strPassword = Application.InputBox(msg, Type:=2)
Select Case strPassword 'change Joe, Alexis, etc as needed for each user and password
Case "Joe": strView = "Joe"
Case "Alexis": strView = "Alexis"
Case "Sophia": strView = "Sophia"
Case "Admin": strView = "View All"
Case Else:
MsgBox ("Your password does not match!"), vbExclamation
Exit Sub
End Select
ActiveWorkbook.CustomViews(strView).Show
End If
End If
End_it:
Sheet1.Protect "Secret"
End Sub
The password for the sheet is "Secret"
"Admin" is the password to show all rows.
Show Views by Password.xlsm
Bookmarks