MrExcel Publishing
Your One Stop for Excel Tips & Solutions

macro to force user to input data in a range


Posted by Ross on May 22, 2001 1:58 PM

How do I write a macro which will return the user to a range whenever the user tries to enter data in a cell? For example, say the range from a1:d4 is called MyRange, and a user tries to enter data in e1. Upon trying to enter data in e1, is there a way for a macro to force the user to return to MyRange?


Posted by Dave Hawley on May 22, 2001 5:42 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 = "$E$1" Then
Application.Undo
Range("MyRange").Select
End If
End Sub


Dave


OzGrid Business Applications