CODEWITHSUNDEEP
excelvba
Salus
Salus

Reputation: 3

Unconfigured {TAB} in Microsoft Excel

I am currently creating an Excel spreadsheet with VBA macros. The thing is that I was trying to force a specific jump in cells when ENTER or TAB is pressed over this cells.

I don't know what happened but now, every time i press the TAB key, Excel prompts me a message saying "The macro Tab1 couldn't be found on: /PathOfTheWorksheet!Tab1"

This is the code I'm trying to use, but the message is still appearing. 

Sub auto_open()
        Application.OnKey "{TAB}", "Tab1"
End Sub
Sub auto_close()
    Application.OnKey "{TAB}"
End Sub
Sub Tab1()
    On Error Resume Next
    If ActiveSheet.Name = "Formulario" Then
'Según la celda actual (posición inicial) elige la celda destino (posición final)
        Select Case ActiveCell.Address(0, 0)
            Case "J11": [J26].Activate
            Case "J16": [J11].Activate
            Case "J21": [J16].Activate
            Case "J26": [J21].Activate
'Si no está en ninguna celda de interés,
'se ubica en la celda contigua (si no está en la última columna)
            Case Else: If ActiveCell.Column <> Cells.Columns.Count Then ActiveCell.Offset(0, 1).Activate
        End Select
'Si no está en la hoja de trabajo hace lo 'normal'
    Else: If ActiveCell.Column <> Cells.Columns.Count Then ActiveCell.Offset(0, 1).Activate
    End If
End Sub

Upvotes: 0

Views: 44

Answers (1)

Variatus
Variatus

Reputation: 14383

The solution you found is quite ingenious, but I think it may not be exactly what you wanted. There is too much collateral damage. Please install the following code in the code sheet of your "Formulario" tab. You can delete it without ill effect on your workbook if it should disappoint you.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const Clm As Long = 10              ' specifies column J
    Dim Respondents, Targets
    Dim R As Long

    Application.EnableEvents = False
    With Target
        If .Column = 10 Then
            ' match sequence of 'Respondents' and 'Targets'
            Respondents = Array(11, 16, 21, 26)
            Targets = Array(0, 26, 11, 16, 21)
            ' the leading 0 in 'Targets' is insignificant but necessary:
            ' MATCH will find row 11 in the first position of 'Respondents'
            ' which is Targets(1) [= 26]

            On Error GoTo NotApplicable
            R = Targets(WorksheetFunction.Match(.Row, Respondents, 0))
            Cells(R, Clm).Select
        End If

NotApplicable:
    End With
    Application.EnableEvents = True
End Sub

Firstly, this code will not run on any worksheet other than the one in whose code sheet you installed it, that is 'Formulario". Secondly, it will run when you click on any of the target cells (array Targets). Thirdly, it will not take any action when you click elsewhere and, fourthly, it will allow you to use the tab key for its usual purposes.

Upvotes: 1

Related Questions