Programming Undo

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...

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.
Click to expand...

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
Click to expand...

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
 
Keith Willshaw said:
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
Click to expand...

Thanks Keith. I'll work on it.

Andy
 
Back
Top