Excel

mjlaw · #1 03-23-2012, 03:03 PM

I have form on an excel spreadsheet. I would like to fill out the cells in a particular order (A1-A7, then B1-B7, then A10-A17, then B10-B17, and so on). Enter always takes me to the next cell below. Tab always takes me to the next cell to the right. I have created a named range and put those cells in order but enter still always goes down and tab always goes right. I thought a named range would work. Any suggestions. I am not opposed to using VBA if anyone can point me in the right direction.

Thanks,

Jason

JBeaucaire · #2 03-23-2012, 06:29 PM

It's kludgy, but something like this would be. you can put a Worksheet_SelectionChange event into the sheet module to watch what cell you were in LAST and force Excel to select the next cell in a "Map" you create.

Code:

Option Explicit
Dim Prior As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Prior Is Nothing Then
    Range("A1").Select
    Set Prior = Selection
    Exit Sub
End If

Application.EnableEvents = False
Select Case Prior.Address
    Case "$A$1", "$A$2", "$A$3", "$A$4", "$A$5", "$A$6"
        Prior.Offset(1).Select
        Set Prior = Selection
    Case "$A$7"
        Range("B1").Select
        Set Prior = Selection
    Case "$B$1", "$B$2", "$B$3", "$B$4", "$B$5", "$B$6"
        Prior.Offset(1).Select
        Set Prior = Selection
    Case "$B$7"
        Range("A10").Select
        Set Prior = Selection
    Case "$A$10", "$A$11", "$A$12", "$A$13", "$A$14", "$A$15", "$A$16"
        Prior.Offset(1).Select
        Set Prior = Selection
    Case "$A$17"
        Range("B10").Select
        Set Prior = Selection
    Case "$B$10", "$B$11", "$B$12", "$B$13", "$B$14", "$B$15", "$B$16"
        Prior.Offset(1).Select
        Set Prior = Selection
    Case "$B$17"
        Range("A1").Select
        Set Prior = Selection
End Select
Application.EnableEvents = True

End Sub

mjlaw · #3 03-24-2012, 09:59 AM

Thank you for the response,

That works great but I need to figure out one tweak. Once all the cells have been filled out once this automatically goes back to A1. It creates a loop. I need to be able to select any cell at random. There are other sections of the form that will be filled out afterward and I cannot get to them. Also, when updating information in one of these cells I need to be able to go directly to a cell.

I am very new to VBA. I was thinking an If statement along the lines of If cell B17 is blank, run this code, if cell B17 has any text, do not run this code (B17 will always be filled out by the user). That way when I open the form it will take me through these cells in order but once I have filled them out, I can move to another section or update these without getting stuck in the loop. Any suggestions.

Jason

JBeaucaire · #4 03-25-2012, 10:38 PM

Code:

Option Explicit
Dim Prior As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [B17] <> "" Then Exit Sub
If Prior Is Nothing Then
    Range("A1").Select
    Set Prior = Selection
'etc...

Catalin.B · #5 03-26-2012, 10:40 AM

You can do it by changing options in excel: first, go to office button, Options, Advanced--> Editing options, then , at the first option: After pressing enter , move selection to: and choose Direction: right from list (default is down)
Select the needed ranges, (A1-A7, then B1-B7, then A10-A17, then B10-B17, and so on), go to Home tab, Format-Format cells-Protection and make sure that Locked is unchecked. On the review tab, click on protect sheet, then ok (you can set a password if you want). Now, only those unlocked cells can be edited, and the selection will move to right when pressing enter.
The inconvenient in your case, is that you have to return to previous row after completing the first part of current row.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to use named ranges in excel vba?	bosve73	Excel Programming	4	01-25-2012 09:26 AM
load list box from range in excel	Joe Patrick	Word VBA	8	06-07-2011 09:31 AM
Named range drop-down	jgelpi16	Excel	1	04-08-2011 03:08 PM
Can't import home adresses in outlook 2010 from excel named ranges	eekie	Outlook	0	05-14-2010 02:04 PM
Through VBA, export range from Excel to Word	duugg	Word VBA	0	08-24-2009 07:50 PM