我们如何创建一个 VBA 代码,以便我们可以在一列中任何给定单元格的 4 个值之间切换,并且在单击该单元格后不会单击进入任何“编辑”模式?
这 4 个值分别是:Y、N、L 和 G。
我能收集到的唯一代码(如下所示)是有问题的,因为它在单击时进入编辑模式,并且它不允许我使用相同的 VBA 代码对整个列执行切换单元格功能:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row <> 1 Then Exit Sub
Select Case UCase(Target.Value)
Case Is = "Y"
Target.Value = "N"
Case Is = "N"
Target.Value = "L"
Case Is = "L"
Target.Value = "G"
Case Is = "G"
Target.Value = "Y"
End Select
End Sub
----- 感谢回复!请参阅附图。这个想法是为了能够切换任何特色项目的状态。状态列位于 L 列,我需要它在 YNLG 之间切换,而不是使用任何下拉列表。
答案1
尝试这种格式
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const MIN_ROW = 4 'Column Header (Status) is on row 4
Const COL_L = 12
If Target.CountLarge = 1 Then 'If a single cell is selected
If Target.Column = COL_L Then 'If selected cell is in col L
If Target.Row > MIN_ROW Then 'If selected cell row is 5 or higher
With Me
Dim lr As Long, arr As Variant, i As Long
lr = .Cells(Rows.Count, COL_L).End(xlUp).Row 'Last used row in col L
If lr > MIN_ROW Then
arr = .Range(.Cells(MIN_ROW + 1, COL_L), .Cells(lr, COL_L))
For i = 1 To UBound(arr)
Select Case UCase(arr(i, 1))
Case Is = "Y": arr(i, 1) = "N"
Case Is = "N": arr(i, 1) = "L"
Case Is = "L": arr(i, 1) = "G"
Case Is = "G": arr(i, 1) = "Y"
End Select
Next
.Range(.Cells(MIN_ROW + 1, COL_L), .Cells(lr, COL_L)) = arr
End If
End With
End If
End If
End If
End Sub
答案2
您可以尝试这个 VBA 代码,我在将它发布到这里之前已经对它进行了测试,并且它可以运行。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell = Range("A2") Then
If ActiveCell.Value = "Y" Then
ActiveCell.Value = "N"
ElseIf ActiveCell.Value = "N" Then
ActiveCell.Value = "L"
ElseIf ActiveCell.Value = "L" Then
ActiveCell.Value = "G"
ElseIf ActiveCell.Value = "G" Then
ActiveCell.Value = "Y"
End If
End If
End Sub
注意:写入 Y(大写)并按 Enter 结束。然后,无论何时放置单元格指针或单击单元格 A2,值都会从 Y 变为 N,再变为 L,再变为 G,再变为 Y。
我确实希望这对你有帮助。