I need to change what the "Enter" button does once it reaches a certain cell.

[Dmaracic]

Hi Everyone,

I just recently got familiar with VBA and I'm trying to figure it if it may be the best solution for me to accomplish my task. I currently use a foot petal to read measurements on a caliper. The foot petal acts as an "Enter" key and once a measurement is recorded it sends it to the spreadsheet and moves down a row. Now after a certain amount of "Enter" hits how can I program Excel, of a working template, to move over lets say a few rows to the right and couple rows up. Once it has done this I would like to continue to keep doing the rows like it did previously.

Examples would be once I get to A25 to move to B15 and then continue to go B30 and move to D13. Again using the "Enter" to do all this.

Is it possible, since I mentioned It would be very beneficial if didn't have to go back and check the screen every

Thanks in advance if anyone can help me get the basics down on this.

[Domski]

Hi,

Have you tried unlocking those cells and protecting the worksheet with just the 'Select unlocked cells' option set?

I think pressing Enter will take you through the cells in that order if you do.

Dom

[Dmaracic]

Dom,

Thanks for the quick reply. I have not attempted this, I will give it a go here in a few mins. The only Issue i foresee is that I need to repeat the process further down the spreadsheet. Would the menthod still work? Do i need to leave those cells unlocked as-well?

*edit; the freeze did not work. I could not freeze the sections I needed. Any other suggestions that may work?*
Dan

[Domski]

Worth a try to see if it works before going down the VBA route.

Dom

[Dmaracic]

The freezing did not work. I believe VBA could solve my issue, I just do not know how to set it up myself and my knowledge is limited currently.

Dan

[tigeravatar]

Dmaracic,

Attached is a sample workbook using the example you specified in your original post:

Examples would be once I get to A25 to move to B15 and then continue to go B30 and move to D13. Again using the "Enter" to do all this.

The code acting in Sheet1 is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim rngLastReached As Range: Set rngLastReached = Me.Range("A26,B31")
    If Intersect(Target, rngLastReached) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Select Case Replace(Target.Address, "$", vbNullString)
        Case "A26": Me.Range("B15").Select
        Case "B31": Me.Range("D13").Select
    End Select
    
End Sub

With that, the cursor will move to the desired cells using only the Enter key.

Hope that helps,
~tigeravatar

[Dmaracic]

Thank you tigeravatar, It works but only on your spreadsheet maybe 2010 has some compatibility issues. I'll try and see if it works for me tonight, I'll update in the morning.

Dan

[Dmaracic]

Tigeravatar,

This is what I'm working with. Code should be in there as-well.

Every red line i want it to go a green. following your example i added extra rows to make the macro work. In your template it works just fine, In mine it does not however.

Thanks for the help once again

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim rngLastReached As Range: Set rngLastReached = Me.Range("C13,E13,G13,C16,E16,G16,C26,E26,G26")
    If Intersect(Target, rngLastReached) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Select Case Replace(Target.Address, "$", vbNullString)
        Case "C13": Me.Range("E10").Select
        Case "E13": Me.Range("G10").Select
        Case "G13": Me.Range("K10").Select
        Case "C16": Me.Range("E14").Select
        Case "E16": Me.Range("G14").Select
        Case "G16": Me.Range("K14").Select
        Case "C26": Me.Range("E24").Select
        Case "E26": Me.Range("G24").Select
        Case "G26": Me.Range("K24").Select
    End Select
    
End Sub