Is it Possible?

[omairhe]

Hi all. i want to know if its possible to go back one step to the previous Cell. could it be done other than offset...

For example if i am on Cell B2 and then press left arrow key it will change selection to B1 but can it somehow by a Vba go back to B2??

 

[Smitty]

Meaning you don't want Enter to move you to an adjacent cell?

If so, goto Tools-->Options-->Uncheck "After Pressing Enter" (I forget which tab it's on pre-2007). For 2007+ it's in Options-->Advanced.

HTH,

 

[omairhe]

no i only want to run enter and i know that setting also.... the end-user might not always use enter key.... he could use arrow or tab key...
is there a settting some how to disable the arrow keys in a Range Name ?

 

[Smitty]

You could use a change event on a defined range (in this case A1:C5):

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A1:C5")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            Target.Select<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[omairhe]

appreciate it Smitty...
Working good