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

i had a similar requirement. what i did was to fitler by the username
and then protect the header using VBA. i kept that row out of the
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.


---
Message posted from http://www.ExcelForum.com/

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 " ¦b¶l¥ó
¤¤¼¶¼g...
i had a similar requirement. what i did was to fitler by the username
and then protect the header using VBA. i kept that row out of the
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.


---
Message posted from http://www.ExcelForum.com/

forgot to ask a very basic question.

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


---
Message posted from http://www.ExcelForum.com/

We are using excel 2000. Thank you


"icestationzbra " ¦b¶l¥ó
¤¤¼¶¼g...
forgot to ask a very basic question.

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


---
Message posted from http://www.ExcelForum.com/

hi,

i am very sorry, the code i have was written for excel 2002. some of
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 off
for protected pages. this is present in excel 2002 onwards. after you
posted that you work on excel 2000, i tried to get my hands on a PC
which runs on that, and tried to test it. it did not work, as excel
2000 does not recognise it as a valid property.

sorry once again,

mac.


---
Message posted from http://www.ExcelForum.com/

i could not figure out a way to prevent the dropdown from being used in
2000. i found another way - altogether prevent the visibility of the
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 of
header row.

nothing is foolproof in excel and its protection.


---
Message posted from http://www.ExcelForum.com/