move to next cell

[excellence]

Hi,
It's been a while and apologize in advance if I am not obeying the rules, but haven't done vba or macros for a while and never was any good at it but here goes:
I am looking to be able to automate the following:
I have a protected worksheet where the involved cells are g3:k8 where I would like to have the 1st active cell as g3 and offset 1 cell at a time to g8.
It would then go to g4:g8...then to g5 same thing,g6 same,g7 and g8 same. For what it's worth, I have some data validation in each cell, which would limit the user to enter just 1 character. So, if e.g GG was entered and dialog box would come up to tell user only 1 character allowed and user could retry to meet the 1 character limit.

If you choose to rsvp, please be real basic, as I am in preschool with this. Also, on a Mac using the latest office

 

[rollis13]

Not sure if I correctly understood your request but for the movement of the cursor you can have a try with this macro to be pasted in the sheet's module (I don't think that with a Mac there will be any difference):

Option Explicit
Const FRow     As Long = 3                        'first row
Const LRow     As Long = 8                        'last row
Const FCol     As Long = 7                        'first column = column G
Const LCol     As Long = 11                       'last column = column K
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRow  As Long
    Dim myCol  As Long
    myRow = Target.Row
    myCol = Target.Column
    If myRow >= FRow And myRow <= LRow And myCol >= FCol And myCol <= LCol Then
        If myRow = LRow Then
            Cells(FRow, myCol + 1).Select
        Else
            Cells(myRow + 1, myCol).Select
        End If
        If myRow = LRow And myCol = LCol Then Cells(FRow, FCol).Select
    End If
End Sub

 

[excellence]

HI,
Thanks for you response and patience. I copied and pasted your script as shown in the image below. Should I expect that if all is good, it should just work, or is there other steps I need to take.

Not sure if I correctly understood your request but for the movement of the cursor you can have a try with this macro to be pasted in the sheet's module (I don't think that with a Mac there will be any difference):

Option Explicit
Const FRow     As Long = 3                        'first row
Const LRow     As Long = 8                        'last row
Const FCol     As Long = 7                        'first column = column G
Const LCol     As Long = 11                       'last column = column K
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRow  As Long
    Dim myCol  As Long
    myRow = Target.Row
    myCol = Target.Column
    If myRow >= FRow And myRow <= LRow And myCol >= FCol And myCol <= LCol Then
        If myRow = LRow Then
            Cells(FRow, myCol + 1).Select
        Else
            Cells(myRow + 1, myCol).Select
        End If
        If myRow = LRow And myCol = LCol Then Cells(FRow, FCol).Select
    End If
End Sub

 

[rollis13]

I have no idea about how your project is so, as said, the macro handles the movement of the cursor from top to bottom. If there isn't another event Worksheet_Change at work in that sheet, then there won't be problems.

 

[excellence]

I had no reply but can't figure how to delete this non reply

 

[excellence]

I had no reply but can't figure how to delete this non reply

Not sure if I correctly understood your request but for the movement of the cursor you can have a try with this macro to be pasted in the sheet's module (I don't think that with a Mac there will be any difference):

Option Explicit
Const FRow     As Long = 3                        'first row
Const LRow     As Long = 8                        'last row
Const FCol     As Long = 7                        'first column = column G
Const LCol     As Long = 11                       'last column = column K
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRow  As Long
    Dim myCol  As Long
    myRow = Target.Row
    myCol = Target.Column
    If myRow >= FRow And myRow <= LRow And myCol >= FCol And myCol <= LCol Then
        If myRow = LRow Then
            Cells(FRow, myCol + 1).Select
        Else
            Cells(myRow + 1, myCol).Select
        End If
        If myRow = LRow And myCol = LCol Then Cells(FRow, FCol).Select
    End If
End Sub

Very sorry here, but my ignorance precedes me.
I was fooling with that xl2bb and excel crashed horribly...needed to reinstall

Your script is partially working...and I mean this with respect.
But I need it to go to g3 first (which i didn't perhaps make clear at first), then go to h3,i3, j3 k3
then go to g4,h4,i4,j4, k4....and same for line 5, 6 ,7 , 8

The user enters let's say A, then after the entry is accepted it would move to the next cell to the right G3:K3 ETC automatically. As I said previously, I HAVE ENGAGED data validation, so if a user puts in more than 1 character, a warning appears to enter 1 character and the user can retry and make it correct. I just bring this up so that the script wouldn't move to the next cell before the user could change their error, in case this matters.

In short this is a 5x6 group of cells that needs to have 1 character added to each cell before moving right 1 cell...till it reaches the 5th cell where it would go to the next line in g column

It would look like this:

abhr1
fhtio
lop04
jjkrt
ffgty
12ert


THE SCRIPT MOVES TO THE NEXT CELL DOWN WHEN HITTING RETURN KEY INSTEAD OF ACROSS . IT WOULD BE NICE IF all the arrow keys would continue to work I realize that down or right movement can be controlled by excel preferences when hitting return key , but would like it to move right for 5 cells, then move to next row and move right for 5 cells etc. My general pref is for return key to move down,

Ideally, the script moves to the next cell, but if return key is hit, it would move to the next appropriate cell, and when i go back to regular sheet return key would move to next cell down

I couldn't upload image (too big) and xl2bb, enough said

Sorry, but i am very anemic in my knowledge here.
Stay Safe
Regards

 

[rollis13]

I'm getting confused, in post #1 you were asking to move vertical:
I would like to have the 1st active cell as g3 and offset 1 cell at a time to g8. It would then go to g4:g8...then to g5 same thing,g6 same,g7 and g8 same.
but now in post #6 you are asking to move horizontal:
then go to h3,i3, j3 k3 then go to g4,h4,i4,j4, k4....and same for line 5, 6 ,7 , 8

So, this macro will now move your cursor horizontally:

Option Explicit
Const FRow     As Long = 3                        'first row
Const LRow     As Long = 8                        'last row
Const FCol     As Long = 7                        'first column = column G
Const LCol     As Long = 11                       'last column = column K
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRow  As Long
    Dim myCol  As Long
    myRow = Target.Row
    myCol = Target.Column
    If myRow >= FRow And myRow <= LRow And myCol >= FCol And myCol <= LCol Then
        If myCol = LCol Then
            Cells(myRow + 1, FCol).Select
        Else
            Cells(myRow, myCol + 1).Select
        End If
        If myRow = LRow And myCol = LCol Then Cells(FRow, FCol).Select
    End If
End Sub

But I need it to go to g3 first (which i didn't perhaps make clear at first),
If you need to always start from cell G3 when you move from one sheet to another just add this macro to the same module:

Private Sub Worksheet_Activate()
    Cells(3, 7).Select
End Sub

 

[excellence]

HI,
I KNOW I AM OVEREXTENDING MY WELCOME!
A few details if you don't mind.
It appears I need to use the return key after each entry since if i use the R arrow and there is a Data Validation issue ....the script stops working when the Data error is fixed. That's ok, but when I use the return key the cell below the active cell is chosen after hitting return key and VERY QUICKLY goes to the next correct cell h3. So it goes g3>enter data>g4>h4 So if data is entered quickly it could get entered in g4 before h3 and screws things up.

Again apologize for my lack of clarity.

For starting with g3 as the active cell...SORRY BUT HAVING TROUBLE with using the icons ...this doesn't give me the active cell...I must have entered it in wrong spot or wrong way

__________________________________
Private Sub Worksheet_Activate()
Cells(3, 7).Select
End Sub
____________________________________

Option Explicit
Const FRow As Long = 3 'first row
Const LRow As Long = 8 'last row
Const FCol As Long = 7 'first column = column G
Const LCol As Long = 11 'last column = column K
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
Dim myCol As Long
myRow = Target.Row
myCol = Target.Column
If myRow >= FRow And myRow <= LRow And myCol >= FCol And myCol <= LCol Then
If myCol = LCol Then
Cells(myRow + 1, FCol).Select
Else
Cells(myRow, myCol + 1).Select
End If
If myRow = LRow And myCol = LCol Then Cells(FRow, FCol).Select
End If
End Sub

 

[rollis13]

It appears I need to use the return key after each entry since if i use the R arrow and there is a Data Validation issue ....the script stops working when the Data error is fixed.

That right but that's Excel, when you are fixing a Data Validation entry you can't use R arrow any more.

but when I use the return key the cell below the active cell is chosen after hitting return key and VERY QUICKLY goes to the next correct cell

Can't do anything about the imperceptible movement of the cursor. But that is normal, you are using a macro to change Excel's default.
To avoid this jumping of the cursor you could change your setting in Options / Advanced / Editing Options / and in 'After key Enter move:' choose: Left, or leave it 'unchecked', but this change will be valid even outside the involved range.

For starting with g3 as the active cell...SORRY BUT HAVING TROUBLE with using the icons ...this doesn't give me the active cell...

There are no icons to be used when a macro is triggered by an Event. Worksheet_Activate will trigger automatically only when you change sheet to the sheet where it operates. So, if the macro Worksheet_Activate is in Sheet1's module when you move to Sheet2 nothing will happen but when you get back to Sheet1 the cursor will be moved to cell G3.

 

[excellence]

Hi again,
Thanks for the explanation.
I still can't get the script to select cell g3 when opening in sheet 1 where the script lives. I eluded to this in my last post. Do I just cut/paste:

Private Sub Worksheet_Activate()
Cells(3, 7).Select
End Sub


I would think it goes in the beginning of script, but might there be a certain way to edit it? I tried cut/paste as I showed above but it doesn't work


Thanks again