go to cell macro

KrazySmile

New Member
Joined
Jan 9, 2005
Messages
34
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
KrazySmile said:
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

New Member
Joined
Jan 9, 2005
Messages
34
ups :oops:

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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Put this in for that sheet's Worksheet_Change() event:
Code:
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

New Member
Joined
Jan 9, 2005
Messages
34

ADVERTISEMENT

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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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

New Member
Joined
Jan 9, 2005
Messages
34

ADVERTISEMENT

sorry, cant seem to find nothing about it :S

if u can help me, i would apreciate a lot

thanks
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Something like this could be added:
Code:
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

New Member
Joined
Jan 9, 2005
Messages
34
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 o_O

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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Tazguy37 said:
Code:
    Else
        If IsNumeric([K14].Value) Then [K14].Value = [K14].Value + 1
Yes, instead of this, put in
Code:
Target.Offset(1, -1).Select

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

Forum statistics

Threads
1,147,508
Messages
5,741,568
Members
423,667
Latest member
Kai_357

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top