Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cursor Direction
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cursor Direction
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 "WLMPilot" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cursor Direction
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" wrote: 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 "WLMPilot" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cursor Direction
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 "WLMPilot" wrote in message ... 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" wrote: 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 "WLMPilot" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cursor Direction
Thanks for all your help. I will plug it in sometime this week and check it
out. Les "Otto Moehrbach" wrote: 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 "WLMPilot" wrote in message ... 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" wrote: 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 "WLMPilot" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|