Move the Darn Cursor!

[Yevette]

All,

I know it's probably something simple, but I've been struggling to find VBA in the forum that will move the cursor down 8 rows (same column) if the cell contains the word "Other" from a drop down list. If E1 = "Other" then Select E9 (so that the user can enter data in E9). But then the user needs to be able to continue entering data in the spreadsheet. I need this to work with 2 contiguous columns (E:F), so the range is E1:F5 (moving down to range E9:F13, respectively - but only if the cell above is changed to "Other").

It seems like it should be simple but every code I've tried does nothing to move the cursor to where I want it.

Thank you for your help.

 

[p45cal]

Activecell.offset(8).select

or a bit more:

if activecell.value = "Other" then Activecell.offset(8).select

 

[Yevette]

Hi P45

Thank you for your reply. I have tried ActiveCell... in the following code just to see if I can get the cursor to move and it doesn't.

If Range ("E122").Value = "Other" Then
ActiveCell.Offset(8,0).Select
End If

Is there something missing from my code? I have it in Worksheet_SelectionChange(ByVal Target As Range). Do I need to somehow set up a Target?

Thanks so much for your help.

 

[p45cal]

The highlighted cell certailnly should move down 8 cells from it's original location if E122 is 'Other'. It's original location is not necessarily E122. So ensure that E122 really does contain 'Other' by typing in the Immediate Pane:
?Range("E122")="Other"
and pressing Enter, hopefully it will return True. Once established, run the code again, but make a note of which cell is currently selected before and after running the ode - it should hae moved 8 cells down.

 

[Yevette]

Hi P45,

Thanks again for your reply. Sorry about the E122 (I'm actually using Range E122:F126 but used 1:5 for an easier example). Anyway, I've ensured that the correct cell contains "Other", but the cursor doesn't move. All other code works for other things. So I know macros is on and working. Am I solely putting "ActiveCell.Offset(8.0).Select" in the VBA project window? Dont' I have to add code that says if E1:F5 = "Other", then ActiveCell.Offset(8.0).Select.

Thanks for your patience.

 

[p45cal]

I think you want to use the change event, not the selection change event. Remove any Selection change event code and try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E122:F12[B][COLOR=Red]6[/COLOR][/B]")) Is Nothing Then
  If Target.Value = "Other" Then Target.Offset(8).Select
End If
End Sub

The red 6 is from your previous post, I'm not sure if it's a typo.
(The Target of the Selection change event is the cell you've just moved TO, not the cell you've just moved FROM)

 

[Yevette]

It's me again, P45...

Okay, here's what's happening. I put in the code exactly as you suggested. When I select "Other" from the drop down list in E122, the cursor blinks once but doesn't move from E122. If I click outside of the cell and then click back into cell E122 the cursor then leaves E122 and moves down to E130. But I want the cursor to move right after "Other" is selected and only move once (for the user to input data). I have clicked on E122 a couple of times now (with "Other" in the cell) and the cursor jumps down to E130.

I hope I explained the result correctly.

 

[p45cal]

Looks like you're still using
Worksheet_SelectionChange
instead of
Worksheet_Change
in the first line of the sub

 

[Yevette]

Uh yes, that would be correct... User error on my part! LOL

Once I followed your directions to a T, it worked perfectly! Thanks so much for your help and patience! Your a gem!