Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros - navigating
How do I make a macro force my spreadsheet to jump to the next cell after I
hit enter. For ex If i'm in cell a2 and I hit enter I want my cursor to go to b5. After I hit enter when I'm in b5 I want the cursor to go to c7 etc etc? Thanks Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros - navigating
Jeff
If you unlock those cells and protect the sheet, then you can tab from unprotected cell to unprotected cell. Enter still doesn't work like that, though. If that isn't something you want to do, try this http://www.dicks-blog.com/archives/2...rder-of-cells/ -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Jeff wrote: How do I make a macro force my spreadsheet to jump to the next cell after I hit enter. For ex If i'm in cell a2 and I hit enter I want my cursor to go to b5. After I hit enter when I'm in b5 I want the cursor to go to c7 etc etc? Thanks Jeff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros - navigating
Thanks,
I used the link you provided and that's exactly what I needed. Thanks "Dick Kusleika" wrote: Jeff If you unlock those cells and protect the sheet, then you can tab from unprotected cell to unprotected cell. Enter still doesn't work like that, though. If that isn't something you want to do, try this http://www.dicks-blog.com/archives/2...rder-of-cells/ -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Jeff wrote: How do I make a macro force my spreadsheet to jump to the next cell after I hit enter. For ex If i'm in cell a2 and I hit enter I want my cursor to go to b5. After I hit enter when I'm in b5 I want the cursor to go to c7 etc etc? Thanks Jeff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros - navigating
On second thought I need a little more help.
Protecting the sheet and unprotecting cells doesn't do it since the tab order doesn't mesh with left to right top to bottom on my form. The link that has the change function looks like it would be perfect but I couldn't get it to work. Here's the code from the link below. I copied this into a module in my spreadsheet. It doesn't seem to execute at all. Not sure if the module is wrong, or more likely how I put it into my excel spreadsheet in the modules that is wrong. Any additional assistance would be appreciated. Jeff Private Sub Worksheet_Change(ByVal Target As Range) Dim aTabOrder As Variant Dim i As Long Set the tab order of input cells aTabOrder = Array(A1³, C1³, G3³, B5³, E1³, F4³) Loop through the array of cell address For i = LBound(aTabOrder) To UBound(aTabOrder) If the changed cell is in the array If aTabOrder(i) = Target.Address(0, 0) Then If the changed cell is the last array element If i = UBound(aTabOrder) Then Select the first cell in the array Me.Range(aTabOrder(LBound(aTabOrder))).Select Else Select the next cell in the array Me.Range(aTabOrder(i + 1)).Select End If End If Next i End Sub "Jeff" wrote: Thanks, I used the link you provided and that's exactly what I needed. Thanks "Dick Kusleika" wrote: Jeff If you unlock those cells and protect the sheet, then you can tab from unprotected cell to unprotected cell. Enter still doesn't work like that, though. If that isn't something you want to do, try this http://www.dicks-blog.com/archives/2...rder-of-cells/ -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Jeff wrote: How do I make a macro force my spreadsheet to jump to the next cell after I hit enter. For ex If i'm in cell a2 and I hit enter I want my cursor to go to b5. After I hit enter when I'm in b5 I want the cursor to go to c7 etc etc? Thanks Jeff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros - navigating
"Jeff" wrote:
Protecting the sheet and unprotecting cells doesn't do it since the tab order doesn't mesh with left to right top to bottom on my form. Have you tried using OnKey? The following call: Application.OnKey "~", "YourNavigationProcedure" will hijack the enter key. After you call this method, YourNavigationProcedure will execute when someone hits Enter, rather than doing the normal Enter thing (however you have that configured.) Application.OnKey "~" Resets the default behavior of the Enter key. Then all you'd have to do is write YourNavigationProcedure and figure out how you know when it's time to set the Enter key back to doing its normal thing. This seems like a rather risky approach, though. You can really frustrate your users by messing with the basic functionality of the application. On the other hand, April 1 is approaching... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros - navigating
Jeff
That sub goes into the worksheet's module, not a standard module. In the VBE's Project Explorer, you should have a module called ThisWorkbook, a module for every sheet named Sheet1, Sheet2, etc. (unless you changed them) and any standard or class modules you added. The sheet module that corresponds to your form is the module for this code. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Jeff wrote: On second thought I need a little more help. Protecting the sheet and unprotecting cells doesn't do it since the tab order doesn't mesh with left to right top to bottom on my form. The link that has the change function looks like it would be perfect but I couldn't get it to work. Here's the code from the link below. I copied this into a module in my spreadsheet. It doesn't seem to execute at all. Not sure if the module is wrong, or more likely how I put it into my excel spreadsheet in the modules that is wrong. Any additional assistance would be appreciated. Jeff Private Sub Worksheet_Change(ByVal Target As Range) Dim aTabOrder As Variant Dim i As Long 'Set the tab order of input cells aTabOrder = Array("A1?, "C1?, "G3?, "B5?, "E1?, "F4?) 'Loop through the array of cell address For i = LBound(aTabOrder) To UBound(aTabOrder) 'If the changed cell is in the array If aTabOrder(i) = Target.Address(0, 0) Then 'If the changed cell is the last array element If i = UBound(aTabOrder) Then 'Select the first cell in the array Me.Range(aTabOrder(LBound(aTabOrder))).Select Else 'Select the next cell in the array Me.Range(aTabOrder(i + 1)).Select End If End If Next i End Sub "Jeff" wrote: Thanks, I used the link you provided and that's exactly what I needed. Thanks "Dick Kusleika" wrote: Jeff If you unlock those cells and protect the sheet, then you can tab from unprotected cell to unprotected cell. Enter still doesn't work like that, though. If that isn't something you want to do, try this http://www.dicks-blog.com/archives/2...rder-of-cells/ -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Jeff wrote: How do I make a macro force my spreadsheet to jump to the next cell after I hit enter. For ex If i'm in cell a2 and I hit enter I want my cursor to go to b5. After I hit enter when I'm in b5 I want the cursor to go to c7 etc etc? Thanks Jeff |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros - navigating
or to get there a shorter way (IMHO) right mouse click on the sheet tab
where you want to use the code on and choose view code - you'll be in the right place then. Cheers JulieD "Dick Kusleika" wrote in message ... Jeff That sub goes into the worksheet's module, not a standard module. In the VBE's Project Explorer, you should have a module called ThisWorkbook, a module for every sheet named Sheet1, Sheet2, etc. (unless you changed them) and any standard or class modules you added. The sheet module that corresponds to your form is the module for this code. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Jeff wrote: On second thought I need a little more help. Protecting the sheet and unprotecting cells doesn't do it since the tab order doesn't mesh with left to right top to bottom on my form. The link that has the change function looks like it would be perfect but I couldn't get it to work. Here's the code from the link below. I copied this into a module in my spreadsheet. It doesn't seem to execute at all. Not sure if the module is wrong, or more likely how I put it into my excel spreadsheet in the modules that is wrong. Any additional assistance would be appreciated. Jeff Private Sub Worksheet_Change(ByVal Target As Range) Dim aTabOrder As Variant Dim i As Long 'Set the tab order of input cells aTabOrder = Array("A1?, "C1?, "G3?, "B5?, "E1?, "F4?) 'Loop through the array of cell address For i = LBound(aTabOrder) To UBound(aTabOrder) 'If the changed cell is in the array If aTabOrder(i) = Target.Address(0, 0) Then 'If the changed cell is the last array element If i = UBound(aTabOrder) Then 'Select the first cell in the array Me.Range(aTabOrder(LBound(aTabOrder))).Select Else 'Select the next cell in the array Me.Range(aTabOrder(i + 1)).Select End If End If Next i End Sub "Jeff" wrote: Thanks, I used the link you provided and that's exactly what I needed. Thanks "Dick Kusleika" wrote: Jeff If you unlock those cells and protect the sheet, then you can tab from unprotected cell to unprotected cell. Enter still doesn't work like that, though. If that isn't something you want to do, try this http://www.dicks-blog.com/archives/2...rder-of-cells/ -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Jeff wrote: How do I make a macro force my spreadsheet to jump to the next cell after I hit enter. For ex If i'm in cell a2 and I hit enter I want my cursor to go to b5. After I hit enter when I'm in b5 I want the cursor to go to c7 etc etc? Thanks Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Navigating | Excel Worksheet Functions | |||
Navigating | Excel Worksheet Functions | |||
navigating with the tab key | New Users to Excel | |||
Navigating | Excel Discussion (Misc queries) | |||
Navigating | Excel Programming |