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

 

[KrazySmile]

errr... how about my last question ?
that enter key thingy?

tnkas

 

[Tazguy37]

Okay, this should work for you:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
    
    Application.EnableEvents = False
    Set isect = Application.Intersect(Target, Range("A1:Z50"))  'you can use a named range here too
    If isect Is Nothing Then    'changed cell not in the range
        GoTo exitMe             'so exit
    Else
        Target.Offset(1, -1).Select
    End If
exitMe:
    Application.EnableEvents = True
End Sub

Put that in the sheet module that you want this to take effect on, it should do what you want. You can leave the range hard coded (as I've done here) or use a variable and let the user select it (say, with an InputBox), whatever you want. Hope that helps!

 

[KrazySmile]

u already got that code to me before.

and like i said before, it work fine with some conditions!

-try put that last code into a sheet, run it, and now type something in the first collum within the range given - error

-select a bunch of cells then press delete - (if it comes out of the sheet) - error

is there a some way to figure out that if the
Target.Offset(1, -1).Select
goes out of the sheet, simply dont run the code!

i think thats what u try to do with the /* Is Nothing Then */ (think, because really i dont understand that line), but if it is, it aint working

thanks again

PS: after the window saying the error, and i press end, of after i stop the debbuger, the macro dont run any more, because it dint reach for the:
Application.EnableEvents = True

how can i enable event again?

 

[Tazguy37]

You want to go to Column IV from Column A? I know there's a less complicated way than checking for that specific column, but I can't recall it right now. If not, just change your range to B1:Z50.

You could also do something like this:

    If Target.Column = 1 Or Target.Count <> 1 Then GoTo exitMe
    
exitMe:
    Application.EnableEvents = True

That will just exit the change routine if you're in column A, or if more than 1 cell is selected.

 

[KrazySmile]

thanks a lot! it was exacly that!

one last thing:

if u want more than one range, meaning, B1:B100, D1:D100, etc.. ?
(note that they r not consecutive);

if ; else; if else; if else; if else ? lol
or another way?

thanks again

 

[Tazguy37]

You can use multiple ranges at the same time:

Range("C5:D9,G9:H16,B14:D18")

Or a Union method example:

    Dim r1, r2, myMultipleRange As Range
    Set r1 = Sheets("Sheet1").Range("A1:B2")
    Set r2 = Sheets("Sheet1").Range("C3:D4")
    Set myMultipleRange = Union(r1, r2)

Also have a look in the VBA help under "Referring to Multiple Ranges". Hope that helps!

 

[KrazySmile]

thank u very much! ray:

sorry about the long thread!

heres the final result, i change it a bit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aux

    Application.EnableEvents = False
    
    If Target.Column = 1 Or Target.Count <> 1 Then GoTo exitMe
    
    aux = Target.Column Mod 2
     
    If (aux = 0) Then Target.Offset(1, -1).Value = Target.Offset(0, -1).Value

exitMe:
    Application.EnableEvents = True

End Sub