Macro to force user to return to designated range?

Hi Ross

Right click on the sheet name tab and select "View Code", paste in this code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("MyRange")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Application.Undo
Range("MyRange").Select
Application.EnableEvents = True
End If
End Sub

Push Alt+Q to return to Excel and save.

Dave

OzGrid Business Applications

It would be better to use Worksheet_SelectionChange so that the user would be unable to select a cell outside of MyRange :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("MyRange")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Range("MyRange").Select
Application.EnableEvents = True
End If
End Sub

However, neither of these procedures prevents a user from dragging contents of cells from MyRange into other cells. Nor do they prevent copying a range of cells from MyRange and pasting it outside of MyRange(provided the active cell of the destination range is within MyRange).
To prevent dragging and copying outside of MyRange, in addition to the Worksheet_SelectionChange procedure above, the following procedure could be used :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Selection
If Intersect(cell, Range("MyRange")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Application.Undo
Range("MyRange").Select
Exit For
End If
Next
Application.EnableEvents = True
End Sub

Ross, if you want to go that far you really should Unlock the cells in MyRange and apply sheet protection (much safer). The above code can easily be disabled by the user choosing to not run macros or simply disabling Events. Either way the code is rendered useless and the user can do as they please.

Dave

OzGrid Business Applications

Sheet protection is a valid option but does suffer from the disadvantage of making formatting unavailable (it may be, of course, that it is required to lock formatting).
If it is required to have sheet formatting available, then one way is to omit the Worksheet_SelectionChange procedure and just use the Worksheet_Change procedure(the one that also prevents copying and dragging).
Regarding users disabling events - yes, that's a problem and if it is a likely scenario then sheet protection is probably the answer.

Thanks Dave and Celia - I appreciate your response

You can if you wish disable the workbook from
running if disabled macro is selected OR the user
presses the shift or escape key while openning.
One line of code in 2 event procedures will do this.
If interested then email me.

Ivan

Ivan
Yes, but the problem still remains of disabling macros and/or disabling events AFTER the workbook has been opened.
Celia You can if you wish disable the workbook from

Celia
The code I suggested will NOT show the workbook
if you disable the macros, so the user cannot
do anything to it.
I'll send you an example.....Try disabling the macros and see what happens.

Ivan

Celia
The code I suggested will NOT show the workbook
if you disable the macros, so the user cannot
do anything to it.
I'll send you an example.....Try disabling the macros and see what happens.

Ivan