Programming Undo
 

Hi everybody.

I am writing a worksheet_change event and need to effectively put in the
code that I describe as "Do not allow change to be entered"

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
username = ActiveWorkbook.BuiltinDocumentProperties("Author")
If Target.Column = 15 and username < "JoeBloggs" Then
Do not allow change to be entered
MsgBox ("You are not authorised to change this column")

End sub


Any ideas or am I completely barking up the wrong tree?

Thanks

Andy B

Programming Undo
 

<ff wrote in message ...
Hi everybody.

I am writing a worksheet_change event and need to effectively put in the
code that I describe as "Do not allow change to be entered"

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
username = ActiveWorkbook.BuiltinDocumentProperties("Author")
If Target.Column = 15 and username < "JoeBloggs" Then
Do not allow change to be entered
MsgBox ("You are not authorised to change this column")

End sub


Any ideas or am I completely barking up the wrong tree?

Thanks

Andy B


Why not just password protect the sheet ?

Keith

Programming Undo
 

<ff wrote in message ...



Why not just password protect the sheet ?

Keith


Because it's a shared workbook where users enter certain criteria in
specific columns and trigger specific events. I just want to blovk them
from certain columns. Password protecting would not do it as everyone
would
have the same password access.


In theory you could use the SelectionChange event
to detect when a protected range was selected

You could then get the user logon name with a win32 API
and compare it against a lookup table and unlock the cell
if the user is on that list.

The problem is that whatever you do with a macro
can be disabled as simply as turning on macro
protection and saying no when the program asks
if they want to run them.

Keith

Programming Undo
 

<ff wrote in message ...

"Keith Willshaw" wrote in message
...

<ff wrote in message ...



Why not just password protect the sheet ?

Keith


Because it's a shared workbook where users enter certain criteria in
specific columns and trigger specific events. I just want to blovk
them
from certain columns. Password protecting would not do it as everyone
would
have the same password access.


In theory you could use the SelectionChange event
to detect when a protected range was selected

You could then get the user logon name with a win32 API
and compare it against a lookup table and unlock the cell
if the user is on that list.

The problem is that whatever you do with a macro
can be disabled as simply as turning on macro
protection and saying no when the program asks
if they want to run them.

Keith



It's a fairly complex sheet that won't open unless you select to enable
macros. It then disable virtually all menus. For the casual user, it
means
they can only type in the main body of the sheet and cannot for instance
edit their entry, cannot change the comment that picks up the Win32API
username identifying them etc. However, checking each comment in a column
for the user is laborious. Hence, wanting to block the user if their name
does not match the correct criteria name for that particular column.

Any ideas now?

Andy



As I said

use the SelectionChange event
to detect when a protected range was selected

Get the user logon name with a win32 API
and compare it against a lookup table and unlock the cell
if the user is on that list.

You only need check username when the worksheet is
opened and you can cache the proteccted address
information in memory as a public Array and just call
a function on each selection change event to do the
compare and lock/unlock the cell

Keith

Programming Undo
 

"Keith Willshaw" wrote in message
...

<ff wrote in message ...

"Keith Willshaw" wrote in message
...

<ff wrote in message ...



Why not just password protect the sheet ?

Keith


Because it's a shared workbook where users enter certain criteria in
specific columns and trigger specific events. I just want to blovk
them
from certain columns. Password protecting would not do it as
everyone
would
have the same password access.


In theory you could use the SelectionChange event
to detect when a protected range was selected

You could then get the user logon name with a win32 API
and compare it against a lookup table and unlock the cell
if the user is on that list.

The problem is that whatever you do with a macro
can be disabled as simply as turning on macro
protection and saying no when the program asks
if they want to run them.

Keith



It's a fairly complex sheet that won't open unless you select to enable
macros. It then disable virtually all menus. For the casual user, it
means
they can only type in the main body of the sheet and cannot for instance
edit their entry, cannot change the comment that picks up the Win32API
username identifying them etc. However, checking each comment in a
column
for the user is laborious. Hence, wanting to block the user if their
name
does not match the correct criteria name for that particular column.

Any ideas now?

Andy



As I said

use the SelectionChange event
to detect when a protected range was selected

Get the user logon name with a win32 API
and compare it against a lookup table and unlock the cell
if the user is on that list.

You only need check username when the worksheet is
opened and you can cache the proteccted address
information in memory as a public Array and just call
a function on each selection change event to do the
compare and lock/unlock the cell

Keith



Thanks Keith. I'll work on it.

Andy