PDA

View Full Version : Hide/Unhide rows - NT login



SDave
08-24-2010, 10:41 AM
Evening all,

I've scoured multiple forums to no avail.

Is it possible to hide/unhide rows based on a users Windows NT log on/username?!

I have a list of log on ID's/usernames which I aim to put into groups. Essentially if an individuals log in ID/username is found in group1 then all rows containing the word "hello" in column M should be hidden.

Similarly, if an individuals log on ID/username is found in group2 all rows, including those containing "hello" in column M should be made visible.

*Groups or named ranges as they are otherwise known

Any help would be much appreciated.

Thanks

Simon Lloyd
08-25-2010, 12:47 AM
You can extend the Case statements in Workbooks_Open macro to suit
This goes in the Thisworkbook module
Private Sub Workbook_Open()
Dim Ans As Range
Application.ScreenUpdating = False
Sheets("Sheet1").Cells.EntireRow.Hidden = False
On Error GoTo Nxt
Set Ans = Sheets("Sheet1").Columns(1).Find(What:=Environ("username"), After:=Sheets("Sheet1").Range("A1"), LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Ans <> "" Then
Select Case Ans.Offset(0, 1)
Case Is = "Group1"
fWord = "hello"
Call Macro1
Case Is = "Group2"
fWord = "Goodbye"
Call Macro1
End Select
Application.ScreenUpdating = True
Exit Sub
End If
Nxt: MsgBox "No such user Found"
ThisWorkbook.Close False
End Sub
And this goes in a standard module
Public fWord As String
Sub Macro1()
Dim rngFind As Range
Dim strValueToPick As String
Dim rngPicked As Range
Dim rngLook As Range
Dim strFirstAddress As String
Set rngLook = Sheets("Sheet1").Range("M1:M" & Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp).Row)
strValueToPick = fWord
With rngLook
Set rngFind = .Find(strValueToPick, LookIn:=xlValues, lookat:=xlWhole)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Set rngPicked = rngFind
Do
Set rngPicked = Union(rngPicked, rngFind)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If
End With

If Not rngPicked Is Nothing Then
rngPicked.EntireRow.Hidden = True
End If
End Sub
All the above is ssuming everything is on sheet1 and that you have the username i.e ntlABx06 in column A and then in column B Group1....etc