Excel VBA宏帮助-必填单元格

Question

一位友好的团队成员向新用户介绍了您的有用网站。

问题：试图强制excel中的用户在填充I-L列中的单元格之前先填充列(O列(中的单元格。问题在于，并不是列中的每个单元格都需要填写。我发现了一个VBA代码，它在一定程度上有所帮助，但问题是，如果在I-L列中的一个单元格中只有文本之前填写了O列，则仍然会出现弹出窗口(因此，除非行中的所有4个单元格都填写完毕，否则会出现错误(。如前所述，目标是(例如(在填充列I、J、K或L264中的任何单元格之前，首先填充O264

进一步加剧这个问题的是，我需要将其应用于多个行，相信这就是范围所在的位置。然而，在excel中使用范围线并不能像我尝试的那样工作。

以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I:L")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Offset(, -1).Value = "" Then
MsgBox "You must first enter feedback in column ""O"""
Target.Value = ""
Target.Offset(, -1).Select
End If
End If
End Sub

Answer 1

在这种情况下，您可能需要为用户提供更多帮助。你可以通过隐藏依赖单元格、锁定它们、使它们变灰等方式来做到这一点。我的感觉是，每当用户以错误的顺序输入数据时，显示一个消息框有点过于被动。

在下面的示例中，目标单元格被锁定并变灰，直到在"O"列中输入内容为止。如果有多个目标行，还需要创建一个目标行列表。

在适当的工作表后面的代码中，下面的骨架代码应该会让您开始。我包含了几个帮助函数，使代码更清晰：

Option Explicit
Private Const SHEET_PASSWORD As String = "xyz" 'whatever password you choose.
Private Const TARGET_ROWS As String = "2,4,6" 'your target rows, separated by commas.
Private Const TARGET_COLUMN As String = "O"
Private Const DEPENDENT_COLUMNS As String = "I:L"
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Set rng = Intersect(Target, Me.Columns(TARGET_COLUMN))
'Exit routine if we're not in the target column.
If rng Is Nothing Then Exit Sub
'Process the target column cells.
For Each cell In rng.Cells
If IsTargetRow(cell.Row) Then
SetDependentStates cell
End If
Next
End Sub
Private Sub SetDependentStates(cell As Range)
Dim DependentRange As Range
'Define the Dependent range based on passed cell row.
Set DependentRange = Intersect( _
cell.EntireRow, _
Me.Range(DEPENDENT_COLUMNS) _
)
'Lock/unlock and paint Dependent rows, based on
'contents of passed cell.
Application.EnableEvents = False 'prevent capture of change event.
Me.Unprotect SHEET_PASSWORD
With DependentRange.Cells
If Len(cell.Value) = 0 Then
.ClearContents
.Locked = True
With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Else
.Locked = False
With .Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End With
Me.Protect SHEET_PASSWORD
Me.EnableSelection = xlUnlockedCells
Application.EnableEvents = True
End Sub
Private Function IsTargetRow(rowNum As Long) As Boolean
Dim v As Variant
'Tests if the pass row number is in the target row list.
For Each v In Split(TARGET_ROWS, ",")
If CLng(v) = rowNum Then
IsTargetRow = True
Exit Function
End If
Next
End Function
Public Sub InitialiseDependentStates()
Dim v As Variant
Dim cell As Range
'Define your unlocked cells.
'This is a simple example, adjust as you wish.
With Me
.Unprotect SHEET_PASSWORD
.Cells.Locked = False
.Protect SHEET_PASSWORD
.EnableSelection = xlUnlockedCells
End With
For Each v In Split(TARGET_ROWS, ",")
Set cell = Me.Range(TARGET_COLUMN & v)
SetDependentStates cell
Next
End Sub

打开工作簿时，您可能需要初始化依赖状态。在工作簿后面的代码中执行此操作：

Private Sub Workbook_Open()
Sheet1.InitialiseDependentStates 'use whichever sheet you're using.
End Sub