Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
programming help | Excel Discussion (Misc queries) | |||
CD Programming | Excel Discussion (Misc queries) | |||
Programming help | Excel Discussion (Misc queries) | |||
UNDO - how many times can I UNDO? | Excel Worksheet Functions | |||
Why is my undo function in Excel only can undo the last 1 or 2 ch. | Excel Worksheet Functions |