Hide and restrict rows for each user to view his own record only

[Pleo]

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

 

[icestationzbra]

i had a similar requirement. what i did was to fitler by the usernam
and then protect the header using VBA. i kept that row out of th
scrollarea, which prevented the filter arrow from being clicked on.

if you want the code, revert here and i will try to find it for you

 

[Pleo]

I would like to get this. Would you please kindly send me. Besides, I don't
know vba. Please tell me how to implement it too. Thanks much.

 

[icestationzbra]

forgot to ask a very basic question.

which version of excel do you use? 2000, 2002 or 2003

 

[pleo]

We are using excel 2000. Thank you

 

[icestationzbra]

hi,

i am very sorry, the code i have was written for excel 2002. some o
the features that are available in 2002 do not function in excel 2000
there is a feature called AllowFilter which can be toggled on and of
for protected pages. this is present in excel 2002 onwards. after yo
posted that you work on excel 2000, i tried to get my hands on a P
which runs on that, and tried to test it. it did not work, as exce
2000 does not recognise it as a valid property.

sorry once again,

mac

 

[icestationzbra]

i could not figure out a way to prevent the dropdown from being used i
2000. i found another way - altogether prevent the visibility of th
arrow that would allow you to select another value from the filter.

following is a snippet:

Sheet2.Select
Selection.AutoFilter Field:=2, Criteria1:=">=01/06/2004",
visibledropdown:=False

in conjuction with this, when you use the following:

Sheet2.ScrollArea = "A2:Z1000"

it would prevent the down arrow from being visible AND the selection o
header row.

nothing is foolproof in excel and its protection