If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Hide and restrict rows for each user to view his own record only
Source worksheet
[ID] [Name] [Date] [Attend Time] 123 MARY [16-Aug] 09:00 ~ 18:00 124 JOHN [16-Aug] 09:00 ~ 18:00 125 SUE [16-Aug] 09:00 ~ 18:00 123 MARY [17-Aug] 09:00 ~ 18:00 124 JOHN [17-Aug] 09:00 ~ 18:00 125 SUE [17-Aug] 09:00 ~ 18:00 123 MARY [18-Aug] 09:00 ~ 18:00 124 JOHN [18-Aug] 09:00 ~ 18:00 125 SUE [18-Aug] 09:00 ~ 18:00 ...................... ================================================== ========= A user receive this sheet and then he will input ID no. at a cell. [Input ID No.] (Remark: User Mary inputs 123 here) The sheet will display Mary's record only. All other record (rows) will be hide. [ID] [Name] [Date] [Attend Time] 123 MARY [16-Aug] 09:00 ~ 18:00 123 MARY [17-Aug] 09:00 ~ 18:00 123 MARY [18-Aug] 09:00 ~ 18:00 Now I use the filter method only but users can view all records. I want to restrict user to view his record only instead of create user own record sheet for each user. Can I do that? Please help. Thanks |
#2
|
|||
|
|||
Hi, Pleo.
Although this isn't foolproof, it's a start for you. This code looks at what's entered in cell B1 and shows all the records whose Cell A match it. (naturally, without a secondary password, anyone can enter Mary's ID (123) and see her records.) Anyhow, it's a start for you. jeff (note: it assumes data starts in row 3 (cRow = 3); and you must have "***" in the very last row on the sheet. ---------------------------------------------- Sub unHideRow() Dim more, foundValues As Boolean Dim cRow, LastRow As Long Application.EnableEvents = False 'LastRow = ActiveSheet.Cells(Rows.SpecialCells (xlCellTypeLastCell).Count, "A").End(xlUp).Row LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row more = True cRow = 3 While more ActiveSheet.Rows(cRow).Hidden = True cRow = cRow + 1 If cRow = LastRow Then more = False Wend more = True cRow = 3 While more If Range("A" & cRow).Value = Range("B1") Then ActiveSheet.Rows(cRow).Hidden = False cRow = cRow + 1 If cRow = LastRow Then more = False Wend Application.EnableEvents = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address "$B$1" Then Exit Sub unHideRow End Sub ' the following code has to go in your Thisworkbook Private Sub Workbook_BeforeClose(Cancel As Boolean) LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row more = True cRow = 3 While more ActiveSheet.Rows(cRow).Hidden = True cRow = cRow + 1 If cRow = LastRow Then more = False Wend Range("B1") = "" End Sub -----Original Message----- Source worksheet [ID] [Name] [Date] [Attend Time] 123 MARY [16-Aug] 09:00 ~ 18:00 124 JOHN [16-Aug] 09:00 ~ 18:00 125 SUE [16-Aug] 09:00 ~ 18:00 123 MARY [17-Aug] 09:00 ~ 18:00 124 JOHN [17-Aug] 09:00 ~ 18:00 125 SUE [17-Aug] 09:00 ~ 18:00 123 MARY [18-Aug] 09:00 ~ 18:00 124 JOHN [18-Aug] 09:00 ~ 18:00 125 SUE [18-Aug] 09:00 ~ 18:00 ...................... ================================================= ======== == A user receive this sheet and then he will input ID no. at a cell. [Input ID No.] (Remark: User Mary inputs 123 here) The sheet will display Mary's record only. All other record (rows) will be hide. [ID] [Name] [Date] [Attend Time] 123 MARY [16-Aug] 09:00 ~ 18:00 123 MARY [17-Aug] 09:00 ~ 18:00 123 MARY [18-Aug] 09:00 ~ 18:00 Now I use the filter method only but users can view all records. I want to restrict user to view his record only instead of create user own record sheet for each user. Can I do that? Please help. Thanks . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Hide and restrict rows for each user to view his own record only | Pleo | General Discussion | 6 | August 23rd, 2004 08:01 PM |