Solved: Go to next row 1st column when cell selected

[Gil]

Hello
I have a worksheet where I enter data in a,b,c,d,e. I have already changed the 'After pressing enter move direction to right'. What I am looking for now is when it moves to column 'F' to go to the next row down column 'A'. Hope that makes sense. I have looked through the forum but havn't found anything in which to even start to resolve this problem.
:dunno

[mdmackillop]

Select columns A-E and run SetScroll, select a single cell and run SetScroll to remove.


Sub SetScroll()
If Selection.Cells.Count = 1 Then
ActiveSheet.ScrollArea = Cells.address
Application.MoveAfterReturnDirection = xlDown
Else
ActiveSheet.ScrollArea = Selection.address
Application.MoveAfterReturnDirection = xlToRight
End If
End Sub

[Gil]

Hello mdmackillop
That works fine and is exactly what I want. One thing I notice now that I have changed the direction after enter in this workbook it changes for all other workbooks. Or have I missed something
:beerchug:

[p45cal]

I had great so-called fun with this one. Paste the following into the sheet code module for the sheet you want it to work on (make sure mdmackillop (http://vbaexpress.com/forum/member.php?u=87)'s macro hasn't set the scroll area to just columns A:E). (To bring up the sheet's code module, right-click on the sheet tab in question and choose View code.)Private Sub Worksheet_Deactivate()
Application.MoveAfterReturnDirection = xlDown 'your preferred option for other sheets.
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static LastRng As Range
Set xxx = Intersect(Target, Columns("A:F"))
If xxx Is Nothing Then
Application.MoveAfterReturnDirection = xlDown
Else
Application.MoveAfterReturnDirection = xlToRight
If Not LastRng Is Nothing Then
If Target.Column = 6 And LastRng.Column = 5 And Target.Row = LastRng.Row Then Target.Offset(1, -5).Activate
End If
End If
Set LastRng = Target
End Sub

[Gil]

Hello p45cal

That works well and unlike mdmackillop's version does not need to be reset on opening and closing. However it does change the enter direction on all other workbooks. All ideas are very gratefully received.
:think:

[mdmackillop]

This will restore "normal" function when you select another workbook. You can create similar code to select and reset restricted scrolling in any worksheet module.

Private Sub Workbook_Deactivate()
Application.MoveAfterReturnDirection = xlDown
End Sub


and

Private Sub Worksheet_Activate()
ActiveSheet.ScrollArea = Range("A:E").Address
Application.MoveAfterReturnDirection = xlToRight
End Sub

[p45cal]

Hello p45cal

That works well and unlike mdmackillop's version does not need to be reset on opening and closing. However it does change the enter direction on all other workbooks. All ideas are very gratefully received.
:think:mdmackillop's
Private Sub Workbook_Deactivate()
Application.MoveAfterReturnDirection = xlDown
End Sub will rectify that. Note that MoveAfterReturnDirection is a property of that instance of Excel, which can have multiple workbooks; open a new instance of excel and that is not affected.
My solution screws up copy/paste operations on that sheet too..

[Gil]

Hello mdmackillop
Sorry! I am a bit lost with your last 2 pieces of code. Where should I be putting them as I have tried several options & don't seem to be getting anywhere.
Confused :confused:

[mdmackillop]

Hi ,
The clue is in the title

Private Sub Workbook_Deactivate()


This goes in the Workbook module. It will activate when you switch to another workbook.

Private Sub Worksheet_Activate()

This goes into the Worksheet module for which you wish to set the scroll control. It is run when the sheet is activated.

[Gil]

Ok I am getting the idea now. The activate seems to work like this, open the workbook and choose sheet 2, then choose sheet 1 & the enter direction switches on.
Private Sub Worksheet_Activate()
ActiveSheet.ScrollArea = Range("A:E").Address
Application.MoveAfterReturnDirection = xlToRight
End Sub

With the deactivate nothing seems to happen, but, if I take out the Private & run the macro then bingo, it deactivates.

Sub Workbook_Deactivate()
Application.MoveAfterReturnDirection = xlDown
End Sub

I would like it to run automatically

Sorry I am at a snails pace but I am gratefull for your continued support.
:hairpull:

[mdmackillop]

Try this combination
Worksheet

Private Sub Worksheet_Activate()
ActiveSheet.ScrollArea = Range("A:E").Address
Application.MoveAfterReturnDirection = xlToRight
End Sub

Private Sub Worksheet_Deactivate()
Application.MoveAfterReturnDirection = xlDown
End Sub


WorkBook

Private Sub Workbook_Activate()
Sheets(1).ScrollArea = Range("A:E").Address
If ActiveSheet.Name = "Sheet1" Then Application.MoveAfterReturnDirection = xlToRight
End Sub

Private Sub Workbook_Deactivate()
Application.MoveAfterReturnDirection = xlDown
End Sub



Personally, I just use a button.

[Gil]

Hello mdmackillop
This combination works well, however, the deactivate only seems to work if you choose another sheet in that workbook. If you go to another workbook either already open or you open a new book the 'enter' direction remains as activated. I can work with that but if there is a tweek that would be great.
Also from p45cal comment earlier the copy/paste operations out of the preset area do not function.
I can work with that too.
When you refer to a button do you mean 2 command buttons, 1 to run the activate macro & 1 to run the deactivate macro.

[mdmackillop]

Refer back to post #2. Only one button is required.