Cursor Direction

[Guest]

I have an Excel workbook utilizing two sheets: Items & Order.

Items sheet contains Item#, Item Name, Stock Levels (Fully Protected)
Order sheet is a form with protected & unprotected cells. The first two
sections that the user fills out has the cursor moving down after each entry,
which I want.

However, I need the cursor to move left to right in the last section without
having to make the universal change for Excel via Tools > Options > Edit.

I was told via a previous question to use a macro. Being unable to locate
the original question, I need someone to provide me with the code that does
this, being aware that the direction needs to change at a certain point
within the sheet.

I also need help with something I just thought of. The second section of
the Order sheet allows the user to order specific, rarely used items. I am
thinking of putting a prompt to ask if there is anything to be ordered within
this section. If "NO", then I need to have a macro catch this and bypass the
second section and proceed to the third section.

Thanks in advance!!
Les

 

[Otto Moehrbach]

The type of macro that you are referring to is called an event macro. In
your case the event would be any entry made in any cell. This would trigger
the macro to run.
The code that is written in the macro would then check to see if the
Target cell (the cell that changed) is in this range or in that range or in
the other range, whatever. The code would then move the cursor like you
want it moved for the particular range. If this sounds like what you want,
post back and provide some detail about the layout of your data and what you
want (cursor movement direction) for each range you designate. The ranges
don't have to be locked in stone, but Excel will need to have some means of
determining the ranges and what ranges go with what cursor movement. HTH
Otto

 

[Guest]

Here is the layout. User enters info in the following cells:

First Section: B2, B3, B4 (downward movement)

Second Section: (Down E col, then to F7 and down, then to G7-G9, then H7-H9
E7 F7 G7 H7
E8 F8 G8 H8
E9 F9 G9 H9

Third Section: (across then down)
A15 -> B15
A16 -> B16
Continues to A90 ->B90

I am considering inserting a prompt just before section two that ask a
yes/no question. Can you provide the code that captures the answer in D2 and
bypasses section two if "no"?

Thanks for your help,
Les

 

[Otto Moehrbach]

Les
The macro below assumes that you have set the default cursor movement as
Down.
The macro does nothing if the entries are in B2 or B3,
If the entry is in B4, the code will display a message box and ask something
and wait for a Yes/No answer. Whatever the response is (Yes or No), the
response will be placed in D2. If the response is Yes, the cursor will be
placed in cell E7. If No, the cursor will be placed in cell A15.
If the entry is in Column A between rows 15 & 90, the cursor will move to
Column B. If the entry is in Column B, the cursor will be moved to Column A
of the next row.
Note that this macro is a sheet macro and must be placed in the sheet module
of the pertinent sheet. To access that module, right-click on the sheet
tab, select View Code. Paste this macro into that module. "X" out of the
module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ans As Long
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub

'Entry in B4
If Target.Address(0, 0) = "B4" Then
Ans = MsgBox("Ask something.", 4, "MsgBox Title here")
Application.EnableEvents = False
If Ans = vbYes Then
Range("D2").Value = "Yes"
Range("E7").Select
Else
Range("D2").Value = "No"
Range("A15").Select
End If
Application.EnableEvents = True
Exit Sub
End If

'Entry in E9:G9
If Not Intersect(Target, Range("E9:G9")) Is Nothing Then
Target.Offset(-2, 1).Select
Exit Sub
End If

'Entry in H9
If Target.Address(0, 0) = "H9" Then
Range("A15").Select
Exit Sub
End If

'Entry in A15:B90
If Not Intersect(Target, Range("A15:B90")) Is Nothing Then
If Target.Column = 1 Then
Target.Offset(, 1).Select
Else
Target.Offset(1, -1).Select
End If
End If
End Sub

 

[Guest]

Thanks for all your help. I will plug it in sometime this week and check it
out.

Les