Hi,
I have a workbook that contains 3 locked sheets, users
can only add data to the sheets via a data entry form. On
my menu list I've added a button that turns on and off the
Auto Filter, and I turn on and off the sheet protection
when row one is selected, the procedure that turns on and
off the sheet protection is in the SheetSelectionChange()
of my class module that is set to Application.
My dilema is that I don't want users to change the
column widths or the contents or in fact make any change
to sheet while protection is disabled, all they should do
is just select the appropriate AutoFilter, but user may
press a key by accident. I know I can write code to put
the entire sheet and the contents of row one (Headers) to
what they should be, but I was wondering of the was a way
to use something like a procedure call Cell_Exit thus I
can see if the cell.row is 1 and if the contents have
change the cell.value = cell.oldvalue cell.columnwidth = 10
etc.

TIA
regards
KM

Kevin,
to trap user changes, try:
Private Sub Worksheet_Change(ByVal Target As Range)
on a Worksheet module
or
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
in the ThisWorkbook module

This will not pick up on column size changes, though. You might have to
check those explicitly.

You can have protection and autofiltering at the same time, though!
Use protection with UserInterfaceOnly on each sheet. (Review the Help for
rules on setting and resetting UserInterfaceOnly protection)

For instance, on my sheet activate event, I use:

Me.EnableAutoFilter = True
Me.Protect Userinterfaceonly:=True
which does the trick. Protected Sheet, but with AutoFiltering!.

Also, I could be corrected, but XL2002 might just have more advanced
lock/protect options than XL2000 (which I am running).

Alex J


"Kevin McCartney" wrote in message
...
Hi,
I have a workbook that contains 3 locked sheets, users
can only add data to the sheets via a data entry form. On
my menu list I've added a button that turns on and off the
Auto Filter, and I turn on and off the sheet protection
when row one is selected, the procedure that turns on and
off the sheet protection is in the SheetSelectionChange()
of my class module that is set to Application.
My dilema is that I don't want users to change the
column widths or the contents or in fact make any change
to sheet while protection is disabled, all they should do
is just select the appropriate AutoFilter, but user may
press a key by accident. I know I can write code to put
the entire sheet and the contents of row one (Headers) to
what they should be, but I was wondering of the was a way
to use something like a procedure call Cell_Exit thus I
can see if the cell.row is 1 and if the contents have
change the cell.value = cell.oldvalue cell.columnwidth = 10
etc.

TIA
regards
KM

Hi Alex,
I forgot to mention, this problem is in Excel 97, the
company moves to XP and Office 2003 next year hence this
solution is just temporary. Although there are some
locations in Europe that still use 0S2 Warp and Excel 5.0
yep Office 4.3 and yes I am from this planet and current
time zone heheheh, anyway thanks for the tip but I'm using
the SheetSelectionChange but I don't think there is a
Selection_Exit, I think I just reformat the sheet ie
Columns("A:AB").width = 11 and then iterate throught is
cell from A1 to AB1 and re enter the text and format the
cell. Its a shame that I can't trust the users to just
click on the auto filter and not change width content
color, naja if users weren't individual I'd lead a boring
life.

Ok thanks
ciao
KM

-----Original Message-----
Kevin,
to trap user changes, try:
Private Sub Worksheet_Change(ByVal Target As Range)
on a Worksheet module
or
Private Sub Workbook_SheetChange(ByVal Sh As Object,
ByVal Target As Range)
in the ThisWorkbook module

This will not pick up on column size changes, though. You
might have to
check those explicitly.

You can have protection and autofiltering at the same
time, though!
Use protection with UserInterfaceOnly on each sheet.
(Review the Help for
rules on setting and resetting UserInterfaceOnly
protection)

For instance, on my sheet activate event, I use:

Me.EnableAutoFilter = True
Me.Protect Userinterfaceonly:=True
which does the trick. Protected Sheet, but with
AutoFiltering!.

Also, I could be corrected, but XL2002 might just have
more advanced
lock/protect options than XL2000 (which I am running).

Alex J


"Kevin McCartney"
wrote in message
...
Hi,
I have a workbook that contains 3 locked sheets, users
can only add data to the sheets via a data entry form.
On
my menu list I've added a button that turns on and off
the
Auto Filter, and I turn on and off the sheet protection
when row one is selected, the procedure that turns on
and
off the sheet protection is in the SheetSelectionChange
()
of my class module that is set to Application.
My dilema is that I don't want users to change the
column widths or the contents or in fact make any change
to sheet while protection is disabled, all they should
do
is just select the appropriate AutoFilter, but user may
press a key by accident. I know I can write code to put
the entire sheet and the contents of row one (Headers)
to
what they should be, but I was wondering of the was a
way
to use something like a procedure call Cell_Exit thus I
can see if the cell.row is 1 and if the contents have
change the cell.value = cell.oldvalue cell.columnwidth
= 10
etc.

TIA
regards
KM


.