MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to force user to return to designated range?


Posted by Ross on May 22, 2001 8:40 PM

Range is A1:C3 and called MyRange. If a user attempts to enter data into any other cell in the spreadsheet, can a macro be activated to force the user to return to MyRange for data input?


Posted by Dave Hawley on May 22, 2001 8:58 PM


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

Posted by Celia on May 23, 2001 12:08 AM


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

Posted by Dave Hawley on May 23, 2001 1:13 AM

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

Posted by Celia on May 23, 2001 1:54 AM

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.

Posted by Ross on May 23, 2001 4:49 AM

Thanks Dave and Celia - I appreciate your response

Posted by Ivan Moala on May 23, 2001 6:29 AM

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

Posted by Celia on May 23, 2001 3:37 PM

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


Posted by Ivan Moala on May 23, 2001 5:49 PM

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

Posted by Ivan Moala on May 23, 2001 6:03 PM

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