0

I'm trying to fill in a list in a block of cells: D2:E8 moving down. When I get to D8, I want to be able to hit enter (numeric or regular) and move to E2 to start the second column of the list. I realize I can tab, enter, tab, enter..., but, I don't want to! Here's my code:

Sub returntotop()
'

' return_to_top Macro
'

If Selection.Address = "Sheet3!$D$8" Then
     Application.OnKey "~", "ActiveCell.Offset(-6, 1).Select"
End If

End Sub

But, it does nothing.

Community
  • 1
  • 1
  • 1
    Doesn't selecting *D2:E8* and then pressing *ENTER KEY* suit you? Can you clarify what you want to achieve? – L42 Jul 08 '14 at 01:51
  • I know. I could do that, but, I'm lazy and don't want to select them all. I want to be able to send the selection from D8 to E2 when I press Enter. I'm a novice (very novice) VBA "student" and want to learn from this. – Rupedaddy Jul 08 '14 at 22:49

1 Answers1

1

You can design an event procedure to activate E2 anytime you try to go from D8 to D9 - stick the below into your worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static mybool As Boolean
    If Target.Address = Range("D8").Address Then
        mybool = True
        Exit Sub
    End If
    If mybool = True And Target.Address = Range("D9").Address Then
        Range("E2").Activate
    End If
    mybool = False
End Sub

@ Rupedaddy - have you been able to work this out? if not you most likely stuck the above code in the wrong place, and this suggests you may not be familiar with object code modules so let me try to explain better.

Do you see the Project explorer Window? resembles this http://www.excel-vba.com/zi-vbe-project-add-sheet.jpg and should be displayed by default in the left upper end corner of the VBE Window. If so just try and find your way through the tree structure it displays, should be pretty intuitive:

  • find the VBAProject that relates to your workbook of interest
  • below it, explore the "Microsoft Excel Objects" node
  • double-click the subnode relating to your worksheet there. This will open your worksheet object code module in the main code window

Then just stick the above code there - and you should be on your way. This is a requirement for event procedures to be entered in the code module of the object they should be associated with.

Hope this helps

Edit: for this to work for all worksheets, stick the below code in your Workbook object code module

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Static ws As Worksheet
    If Target.Address = Range("D8").Address Then
        Set ws = Sh
        Exit Sub
    End If
    If ws Is Sh Then
        Set ws = Nothing
        If Target.Address = Range("D9").Address Then
            Range("E2").Activate
        End If
    End If
End Sub
IAmDranged
  • 2,890
  • 1
  • 12
  • 6
  • I couldn't get that to work. Does it help to tell you my cells are in Sheet3? – Rupedaddy Jul 08 '14 at 22:50
  • 1
    @Rupedaddy Actually you need to put this in Sheet3 code. – L42 Jul 09 '14 at 00:50
  • Y'all are awesome! I thought I had tried pasting the above in the Sheet3 object, but, apparently I never did. It works now that I have it in there. This makes sense. Thank you all for the help and working with a novice such as myself. I have a long way to go. :) – Rupedaddy Jul 10 '14 at 21:45
  • So, next question; how would one get that to work in all worksheets in the workbook? What location would the code need to go in? Or, what needs to be added to make that happen? – Rupedaddy Jul 11 '14 at 03:55