时间戳

首页 > 解决方案 > Excel VBA 宏帮助 - 必填单元格

excel - Excel VBA 宏帮助 - 必填单元格

问题描述

一位友好的团队成员将新用户推荐到您的有用网站。

问题：试图强制 excel 中的用户在填写 IL 列中的单元格之前先填写列中的单元格（O 列）。问题在于，并非列中的每个单元格都需要填写。我发现了一个有一定帮助的 VBA 代码，但问题是如果在只有一个文本之前填写 O 列，仍然会出现弹出窗口列 IL 中的单元格（因此除非填写该行中的所有 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

标签： excelvba

解决方案

This could be a case where you might need to aid the user a little more. You could do that by hiding the dependent cells, by locking them, by greying them out, etc. My feeling is that displaying a message box whenever a user enters data in the wrong order is a little too reactive.

In the example below, the target cells are locked and greyed until something is entered in column 'O'. You'd also need to create a list of target rows if you have more than one.

In your code behind the appropriate sheet, the skeleton code below should get you started. I've included a couple of helper functions to make the code a little clearer for you:

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

You'll likely want to initialise the dependent states when the workbook is opened. Do this in the code behind the Workbook:

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

推荐阅读