Hide contents of specified row for some users

jovoga

New Member
Joined
Apr 9, 2011
Messages
15
Hello,

Is possible to hide contents of specified cell..for example if in row is Facility (see table below), that only some defined USERS can see contents of entire row?
Please help
TaskDepartment
Do somethingfacility
Do nothingproduction

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

I am assuming your "Department" field is in column B.

Please see below for two procedures; a function procedure that should be placed in a standard code module and an even-handler procedure that should be placed in the code module corresponding to the worksheet containing your task/department data.

The function procedure gets the username from your OS (e.g. Windows) and determines whether or not the user logged onto the PC is "authorised". The event-handler procedure then hides/unhides certain rows in your worksheet depending on the result of the function procedure.

You will need to read through both procedures first and make changes accordingly. And, more likely than not, you'll need to adapt both procedures to suit your requirements.


Code:
Function Authorised_User() As Boolean

    Dim UserNames(1 To 10) As String
    Dim UserName As String
    Dim i As Byte
    
    'set authorised usernames
    UserNames(1) = "Oliver"
    UserNames(2) = "Jack"
    UserNames(3) = "Harry"
    UserNames(4) = "Jacob"
    UserNames(5) = "Charlie"
    UserNames(6) = "Thomas"
    UserNames(7) = "Oscar"
    UserNames(8) = "William"
    UserNames(9) = "James"
    UserNames(10) = "George"
    
    'get username from OS
    UserName = Environ("UserName")
    
    'check if user is authorised
    For i = LBound(UserNames) To UBound(UserNames)
        If UserName = UserNames(i) Then
            Authorised_User = True
            Exit Function
        End If
    Next i
    Authorised_User = False

End Function


Code:
Private Sub Worksheet_Activate()

    Dim Rang As Range
    Dim Cell As Range
    
    'get used cells in column B
    Set Rang = Intersect(Me.UsedRange, Me.Columns("B"))
    
    'turn off screen updating
    Application.ScreenUpdating = False
    
    'unhide all rows
    Me.Rows.Hidden = False
    
    'if user is not authorised then hide
    'rows with "facility" in column B
    If Authorised_User = False Then
        For Each Cell In Rang
            If LCase(Cell.Value) = LCase("Facility") Then
                Cell.EntireRow.Hidden = True
            End If
        Next Cell
    End If
    
    'turn back on screen updating
    Application.ScreenUpdating = True

End Sub
 

HebertsYES

New Member
Joined
Oct 4, 2014
Messages
15
Select Row you wish to hide, Click Home - Format - Hide Rows. Now if you dont want anyone to change them back to visible, protect your workbook with a password: Click- Review - Protect Workbook . Enter a password, click ok, and you should be all set. hope this helps!
 

jovoga

New Member
Joined
Apr 9, 2011
Messages
15
/CODE][/QUOTE]

Hello,
Thank. Code works.
But I need to run macro automatically after open excel and it dont work.
I try change first row to workbookopen, but I have compile error..Method or data member not found... at row Set Rang = Intersect(Me.UsedRange, Me.Columns("B"))

What do I wrong?
Please help


' Private Sub Worksheet_Activate()
Private Sub Workbook_Open()


Dim Rang As Range
Dim Cell As Range

'get used cells in column B
Set Rang = Intersect(Me.UsedRange, Me.Columns("B"))

'turn off screen updating
Application.ScreenUpdating = False

'unhide all rows
Me.Rows.Hidden = False

'if user is not authorised then hide
'rows with "facility" in column B
If Authorised_User = False Then
For Each Cell In Rang
If LCase(Cell.Value) = LCase("Facility meeting") Then
Cell.EntireRow.Hidden = True
End If
Next Cell
End If

'turn back on screen updating
Application.ScreenUpdating = True


End Sub
Function Authorised_User() As Boolean


Dim UserNames(1 To 10) As String
Dim UserName As String
Dim i As Byte

'set authorised usernames
UserNames(1) = "Oliver"
UserNames(2) = "Jack"
UserNames(3) = "Harry"
UserNames(4) = "Jacob"
UserNames(5) = "Charlie"
UserNames(6) = "Thomas"
UserNames(7) = "Oscar"
UserNames(8) = "William"
UserNames(9) = "James"
UserNames(10) = "George"

'get username from OS
UserName = Environ("UserName")

'check if user is authorised
For i = LBound(UserNames) To UBound(UserNames)
If UserName = UserNames(i) Then
Authorised_User = True
Exit Function
End If
Next i
Authorised_User = False


End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,122,498
Messages
5,596,509
Members
414,073
Latest member
Contilly

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
Top