仅对某一特定列使用宏代码

Question

我对 Excel 编程还是个新手，对代码进行细微更改时遇到了麻烦。我的代码使用户能够选择一个单元格，然后使用向上和向下箭头来选择定义的值。查看下面的代码，我在工作表中调用函数“UpOn​​e”和“DownOne”，然后将保存的字符串值保存在模块中。

工作表代码

Private Sub Worksheet_Open()
    Application.OnKey "{UP}", "UpOne"
    Application.OnKey "{DOWN}", "DownOne"
End Sub

模块代码

Sub DownOne()
    Select Case ActiveCell.Value
    Case ""
        ActiveCell.Value = "PASS"
    Case "PASS"
        ActiveCell.Value = "FAIL"
    Case "FAIL"
        ActiveCell.Value = "Unknown"
 End Sub
 Sub UpOne()
    Select Case ActiveCell.Value
    Case "Unknown"
        ActiveCell.Value = "FAIL"
    Case "FAIL"
        ActiveCell.Value = "PASS"
    Case "PASS"
        ActiveCell.Value = ""
 End Sub

我的问题是如何使该代码仅适用于某一特定列中的所有单元格？当用户选择不同的列时，我将如何获得不同的值。因此，如果用户选择“J”列中的空单元格，他/她可以使用箭头键浏览“A”、“B”、“C”等值，但是当他/她选择“N”列中的空单元格时”，他/她可以浏览一组不同的值，例如“E”、“F”、“G”等。 感谢您的帮助！

Answer 1

与每次选择单元格时禁用/启用代码（另外，您需要满足多单元格选择的要求）相比，使用数据验证会更简单。您可以设置适用于特定单元格的列表。Debra

Dalgleish 在 Contextures

Answer 2

基于您原始方法的粗略代码：

Sub DownOne()
   CycleValue Selection, -1
End Sub
Sub UpOne()
   CycleValue Selection, 1
End Sub

Sub CycleValue(rng As Range, GoDir As Integer)

    Dim arrA, arrB
    Dim arrVals, val
    Dim c As Range, m, indx As Integer
    Dim lb As Integer, ub As Integer
    Dim ProcessThis As Boolean

    arrA = Array("Fail", "Pass", "")
    arrB = Array("Eggs", "Bacon", "Toast", "Beans", "")

    For Each c In rng.Cells
        ProcessThis = True

        'what values are we cycling through?
        'based on column position
        If c.Column = 1 Then
            arrVals = arrA
        ElseIf c.Column = 5 Then
            arrVals = arrB
        Else
            ProcessThis = False 'not checking this column
        End If

        If ProcessThis Then
            lb = LBound(arrVals)
            ub = UBound(arrVals)
            val = Trim(c.Value)
            m = Application.Match(val, arrVals, 0)

            If IsError(m) Then
                indx = lb
            Else
                m = m - 1 '1-based
                indx = m + GoDir
                If indx < lb Then indx = ub
                If indx > ub Then indx = lb
            End If
            c.Value = arrVals(indx)
         End If
    Next c
End Sub