User Access Visibility

[SteynBS]

Good day

I was wondering it is possible to restrict the visible cells to the person currently using the file. If user A opens the file, he can only see the data where his name is assigned in a row. If user B opens the file he can only see data assigned to him.

This is a file in one drive where multiple users has access to it, but we need to restrict what they can see.

 

[HighAndWilder]

Good day

I was wondering it is possible to restrict the visible cells to the person currently using the file. If user A opens the file, he can only see the data where his name is assigned in a row. If user B opens the file he can only see data assigned to him.

This is a file in one drive where multiple users has access to it, but we need to restrict what they can see.

Are these users only wanting to view the data rather than view and edit it?

If the former then you could split the data by user and each user have a separate workbook in their user folder.

How often is the data updated?

 

[SteynBS]

Are these users only wanting to view the data rather than view and edit it?

If the former then you could split the data by user and each user have a separate workbook in their user folder.

How often is the data updated?

Hello HighAndWilder

We suggested to split the data, but they want to keep it in one place - one file. I am not too sure about editing, but definitely viewing. I believe there will be a user that should be able to see everything and the "admins" only see their portion

Data will be updated frequently

 

[Skyybot]

I believe a combination of the Application.UserName property and the Workbook_Open event may work for you.

 

[SteynBS]

I believe a combination of the Application.UserName property and the Workbook_Open event may work for you.

Good day,

How would I go about this? Can we do a VBA?

 

[Skyybot]

As long as the name in the cell matches the username of the computer, something like this (must be placed in ThisWorkbook code module). Remember to test on a COPY of your Workbook. Of course we will have to add some code to the Save and Close event that will reset the Workbook. But see if this will work as a starting point.

Option Explicit

Private Sub Workbook_Open()
Dim sht As Worksheet, nRange As Range, cell As Range, un As String, n As Range, i As Long
'Set the UserName to search for
un = Application.UserName
'Define the Sheet that you want to search for Names in
Set sht = ThisWorkbook.Sheets("Sheet1")
'Unprotect the Worksheet and unlock cells
sht.Unprotect ("pword")
sht.Cells.Locked = False
'Define the Column to search for Name. This searches Column A
Set nRange = sht.Range(Cells(1, 1), Cells(sht.UsedRange.Rows.Count, 1))
'Check each row in Column A for Name match and hide and lock rows that don't match
For Each cell In nRange
    Set n = cell.Find(un, LookAt:=xlPart)
    If n Is Nothing Then
        sht.Rows(cell.Row).Hidden = xlVeryHidden
        sht.Rows(cell.Row).Locked = True
    End If
Next cell
sht.EnableSelection = xlUnlockedCells
sht.Protect ("pword")
End Sub

 

[SteynBS]

As long as the name in the cell matches the username of the computer, something like this (must be placed in ThisWorkbook code module). Remember to test on a COPY of your Workbook. Of course we will have to add some code to the Save and Close event that will reset the Workbook. But see if this will work as a starting point.

Option Explicit

Private Sub Workbook_Open()
Dim sht As Worksheet, nRange As Range, cell As Range, un As String, n As Range, i As Long
'Set the UserName to search for
un = Application.UserName
'Define the Sheet that you want to search for Names in
Set sht = ThisWorkbook.Sheets("Sheet1")
'Unprotect the Worksheet and unlock cells
sht.Unprotect ("pword")
sht.Cells.Locked = False
'Define the Column to search for Name. This searches Column A
Set nRange = sht.Range(Cells(1, 1), Cells(sht.UsedRange.Rows.Count, 1))
'Check each row in Column A for Name match and hide and lock rows that don't match
For Each cell In nRange
    Set n = cell.Find(un, LookAt:=xlPart)
    If n Is Nothing Then
        sht.Rows(cell.Row).Hidden = xlVeryHidden
        sht.Rows(cell.Row).Locked = True
    End If
Next cell
sht.EnableSelection = xlUnlockedCells
sht.Protect ("pword")
End Sub

Thank you, I will have a look at it and test. I will get back to you. Thank you for your time and assistance.

 

[SteynBS]

Thank you, I will have a look at it and test. I will get back to you. Thank you for your time and assistance.

Apologies, not familiar with this section:

'Set the UserName to search for
un = Application.UserName

Can you give me an example what this should look like? and what are the possibility that the vba identifies the user when the file is opened. The idea is when buyer 1 opens the workbook, he can only see his stuff, when buyer 2 opens the file he can only see his stuff. Don't know if this is possible?

 

[SteynBS]

Apologies, not familiar with this section:

'Set the UserName to search for
un = Application.UserName

Can you give me an example what this should look like? and what are the possibility that the vba identifies the user when the file is opened. The idea is when buyer 1 opens the workbook, he can only see his stuff, when buyer 2 opens the file he can only see his stuff. Don't know if this is possible?

View attachment 118321

Hi

As long as the name in the cell matches the username of the computer, something like this (must be placed in ThisWorkbook code module). Remember to test on a COPY of your Workbook. Of course we will have to add some code to the Save and Close event that will reset the Workbook. But see if this will work as a starting point.

Option Explicit

Private Sub Workbook_Open()
Dim sht As Worksheet, nRange As Range, cell As Range, un As String, n As Range, i As Long
'Set the UserName to search for
un = Application.UserName
'Define the Sheet that you want to search for Names in
Set sht = ThisWorkbook.Sheets("Sheet1")
'Unprotect the Worksheet and unlock cells
sht.Unprotect ("pword")
sht.Cells.Locked = False
'Define the Column to search for Name. This searches Column A
Set nRange = sht.Range(Cells(1, 1), Cells(sht.UsedRange.Rows.Count, 1))
'Check each row in Column A for Name match and hide and lock rows that don't match
For Each cell In nRange
    Set n = cell.Find(un, LookAt:=xlPart)
    If n Is Nothing Then
        sht.Rows(cell.Row).Hidden = xlVeryHidden
        sht.Rows(cell.Row).Locked = True
    End If
Next cell
sht.EnableSelection = xlUnlockedCells
sht.Protect ("pword")
End Sub

Hi

I had to use =GetUserName() to get the user name and it works.

Now it shows only my data, but the header is gone, we still need to be able to use the header(row 1) and filter / sort on it if possible please.

 

[Skyybot]

This will keep Header row:

Option Explicit

Private Sub Workbook_Open()
Dim sht As Worksheet, nRange As Range, cell As Range, un As String, n As Range, i As Long
'Set the UserName to search for
un = Application.UserName
'Define the Sheet that you want to search for Names in
Set sht = ThisWorkbook.Sheets("Sheet1")
'Unprotect the Worksheet and unlock cells
sht.Unprotect ("pword")
sht.Cells.Locked = False
'Define the Column to search for Name. This searches Column A
Set nRange = sht.Range(Cells(2, 1), Cells(sht.UsedRange.Rows.Count, 1))
'Check each row in Column A for Name match and hide and lock rows that don't match
For Each cell In nRange
    Set n = cell.Find(un, LookAt:=xlPart)
    If n Is Nothing Then
        sht.Rows(cell.Row).Hidden = xlVeryHidden
        sht.Rows(cell.Row).Locked = True
    End If
Next cell
sht.EnableSelection = xlUnlockedCells
sht.Protect ("pword")
End Sub