Skip logic based on cell entry

[Twee]

Hi all,


I am trying to design a 10 page form in excel and am hobbling through it because I know Access is a better way to go about doing this...Anyway, I'm using Excel due to accessibility to MS Access, but I digress. To make the form flow better, is there a good way to make it skip to next cell depending on what was entered in Cell A1. For instance, if Cell A1 is yes, go to cell A2; if no, Cell A3, and so forth. Thank you for the help in advance.


Twee

 

[Hui]

Twee


When you say Skip or Goto are you talking about the cursor moving there automatically, or when you print "Skipping lines", or when you move around the form using Tab, or a formula in another cell based on the value of cell A1 being yes/no or something else ?

 

[Twee]

Hi Hui,


sorry I wasn't clearer in my question. I simply mean for the cursor to move upon pressing "Enter" to the appropriate cells depending on what was entered previously in cell A1. I hope that makes sense. Thank you for your help.


Twee

 

[keymaster]

It might be far easier to actually make a set of userforms for something like this... or even use Access. AFAIK, You can do this in excel, but the effort would be too much and the model will be inflexible. Please tell me if you come up with an interesting approach to solve this...

 

[Hui]

Twee


You will need to add a couple of macro's to your workbook


add the next 4 lines to the worksheet code page that where you want to monitor the Enter


Private Sub Worksheet_Activate()

Application.OnKey "{ENTER}", "PressEnt" 'This monitors the Numeric Enter button

Application.OnKey "~", "PressEnt" 'This monitors the Main Enter button

End Sub


and add this to a code module


Sub PressEnt()

If Worksheets("Sheet1").Range("A1").Value = "Yes" Then

Worksheets("Sheet1").Range("A3").Select


'Uncomment the next line to goto A3 and Jump Down a page

'ActiveWindow.LargeScroll Down:=1


Else

Worksheets("Sheet1").Cells(ActiveCell.Row + 1, ActiveCell.Column).Select

' This maintains a normal Enter key behaviour if A1 <> "Yes"

End If


End Sub


Change the worksheet names etc as relevent