Hide contents of specified row for some users

[jovoga]

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

Task	Department
Do something	facility
Do nothing	production

<tbody>
</tbody>

 

[ParamRay]

.
.

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.

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

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]

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]

/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