MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to force user to return to defined range


Posted by Ross on May 22, 2001 2:04 PM

For example,range A1:c3 is named MyRange, and a user attempts to enter data in d1. Is there a way to write a macro so that when the user attempts to enter data in d1 the user is forced back to MyRange?


Posted by Barrie Davidson on May 22, 2001 2:07 PM

How about protecting the sheet and leaving your range unlocked? This way users can only enter data in the unlocked range.

Barrie

Posted by Dave Hawley on May 22, 2001 2:25 PM

Hi Ross

Right click on your sheet name tab and select "View Code". Paste in this code:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Then
Application.Undo
Range("MyRange").Select
End If
End Sub


Dave

OzGrid Business Applications