Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent deleting multiple cells at once
How can I prevent a user from deleting more than one cell at a time? I have
code that returns the cell's original contents if a user presses delete, but if they highlight several cells and press delete, it doesn't work (the cells' contents are actually deleted). I added a message box to the code as below, but it doesn't actually prevent them from deleting the cells' contents. If Target.Cells.Count 1 Then MsgBox("You can only edit one cell at a time.") Exit Sub End If Thanks, ~ Horatio |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent deleting multiple cells at once
Horatio
What you have to do is to first determine if Target.Count1. If it is, issue an Undo command like: Application.EnableEvents=False Application.Undo Application.EnableEvents=True This gets your values back. Now you can display the message box. The complete macro would look like: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True MsgBox "Whatever" End If End Sub "Horatio J. Bilge, Jr." wrote in message ... How can I prevent a user from deleting more than one cell at a time? I have code that returns the cell's original contents if a user presses delete, but if they highlight several cells and press delete, it doesn't work (the cells' contents are actually deleted). I added a message box to the code as below, but it doesn't actually prevent them from deleting the cells' contents. If Target.Cells.Count 1 Then MsgBox("You can only edit one cell at a time.") Exit Sub End If Thanks, ~ Horatio |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Prevent deleting multiple cells at once
Thanks. That works great.
"Otto Moehrbach" wrote: Horatio What you have to do is to first determine if Target.Count1. If it is, issue an Undo command like: Application.EnableEvents=False Application.Undo Application.EnableEvents=True This gets your values back. Now you can display the message box. The complete macro would look like: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True MsgBox "Whatever" End If End Sub "Horatio J. Bilge, Jr." wrote in message ... How can I prevent a user from deleting more than one cell at a time? I have code that returns the cell's original contents if a user presses delete, but if they highlight several cells and press delete, it doesn't work (the cells' contents are actually deleted). I added a message box to the code as below, but it doesn't actually prevent them from deleting the cells' contents. If Target.Cells.Count 1 Then MsgBox("You can only edit one cell at a time.") Exit Sub End If Thanks, ~ Horatio |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protect cells in excel to prevent users from deleting formulae | Excel Discussion (Misc queries) | |||
How can I prevent someone from deleting a shared workbook? | Excel Worksheet Functions | |||
How to prevent Excel from deleting leading zeros? | Excel Discussion (Misc queries) | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
How do I prevent someone from deleting a workbook? | Excel Discussion (Misc queries) |