go to cell macro

[KrazySmile]

i have a a sheet that is only for users to input data.

how can i configure that in some cells, after the user press enter, the next cell wont b the next one to the right (as it is configured now) but one down and two to the left,

ex: after i press enter in B2 cell, the next selected cell should b C1;

this has to happen every time (in the cells i want), so i need this macro to run everytime the selected cell changes:

thanks 4 the help

Filipe Silva

 

[Tazguy37]

how can i configure that in some cells, after the user press enter, the next cell wont b the next one to the right (as it is configured now) but one down and two to the left,

ex: after i press enter in B2 cell, the next selected cell should b C1;

From B2 to C1 (at least on my sheet) is 1 row up, 1 column to the right. Which would you like to have happen?

 

[KrazySmile]

ups

sorry, i write the text well, and the example wrong,

ex: after i press enter in B2 cell, the next selected cell should b A3;

sorry

 

[Tazguy37]

Put this in for that sheet's Worksheet_Change() event:

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Offset(1, -1).Select
End Sub

Press Alt-F11 to open the VBE.
Press Control-R to open the Project Explorer.
Click "Microsoft Excel Objects" for the file you're working on.
Double-click the sheet where you need this to work.
Open the Code pane with F7.
Paste the above code in.
Press Alt-Q to close the VBE and return to Excel.

Hope that helps!

 

[KrazySmile]

that works good, but dont verify from wich cell it has come:
meaning, the code runs on every single cell.
i want it to run on some ranges,

thanks

PS: i know vba basis (vary basis :P), dont need that last explanations on how to work with vba

 

[Tazguy37]

You could change it so that if the cell being changed is from column 3 to 8, then go down and to the left, otherwise, just go down. Or, if the cell is in range A8 to G15, then do it. There are several ways to go about this. Have a look at the Intersect Method in the VBA help if you want to take a stab at this on your own.

 

[KrazySmile]

sorry, cant seem to find nothing about it :S

if u can help me, i would apreciate a lot

thanks

 

[Tazguy37]

Something like this could be added:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
    
    Application.EnableEvents = False
    Set isect = Application.Intersect(Target, Range("A8:C19"))  'you can use a named range here too
    If isect Is Nothing Then    'changed cell not in the range
        GoTo exitMe             'so exit
    Else
        If IsNumeric([K14].Value) Then [K14].Value = [K14].Value + 1
    End If
exitMe:
    Application.EnableEvents = True
End Sub

Hope that helps!

 

[KrazySmile]

ok, instead of
If IsNumeric([K14].Value) Then [K14].Value = [K14].Value + 1

i put:
Target.Offset(1, -1).Select

y couldnt u say that and leave it up to me to figure ir out , lol

one las thing:
is there a way to assing this code only to enter key?
for example when i select delete on all cells on sheet, i got an error, cause he is running the macro, and off course giving error:

or i simply solve it by adding this :

Set maxRange = Application.Intersect(Target, Range("A1:Z50"))
If ( maxRange ) Then Target.Offset(1, -1).Select

if i could assing a key, it would b a better code(and i learn to do one more thing :P)

thanks 4 the patience and the help

 

[Tazguy37]

    Else
        If IsNumeric([K14].Value) Then [K14].Value = [K14].Value + 1

Yes, instead of this, put in

Target.Offset(1, -1).Select

Sorry about that, I think I got 2 posts confused.