基于VBA的Excel场景构建器开发技术求助

I’ve put together a complete solution for your scenario builder based on your requirements. Let’s walk through everything you need to set up, including worksheet structure and fully commented VBA code that handles the matching, data syncing, and dynamic dropdown generation.

Step 1: Define Your Worksheet Structure #

First, let’s make sure your worksheets are set up correctly (I’ll use clear names for easier reference):

• Worksheet 1: Rename to AllScenarios
  This sheet stores all your workflow scenarios. Make sure your columns follow this order (adjust the code later if your actual columns differ):

  1. Column A: Task ID
  2. Column B: Task Name
  3. Column C: Role
  4. Column D: In-status
  5. Column E: Operation
  6. Column F: Out-status ID
  7. Column G: Out-status
  8. Column H: SLA
     Remember: Each (Task, Operation, Status) combination is unique here.

• Worksheet 2: Rename to ScenarioBuilder

  • Rows 1-2 are fixed (your header/setup area)
  • Starting at Row 3: Each row represents a step in your scenario
    • Column D: This is where the operation dropdown will live (we’ll set up dynamic data validation here)
    • Assign columns for synced data to match AllScenarios (e.g., Column E = Out-status ID, Column F = Out-status, Column G = SLA, Column H = Task ID, Column I = Task Name, Column J = Role)

Step 2: VBA Code Implementation #

1. Open the VBA Editor: Press Alt + F11 in Excel
2. In the Project Explorer, double-click ScenarioBuilder to open its code window
3. Paste the following code (with detailed comments to help you follow along):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsAllScenarios As Worksheet
    Dim wsBuilder As Worksheet
    Dim lastRowAll As Long
    Dim lastRowBuilder As Long
    Dim i As Long
    Dim currentRow As Long
    Dim prevOutStatus As String
    Dim selectedOperation As String
    Dim uniqueOperations As Collection
    Dim op As Variant
    Dim currentOutStatus As String
    
    ' Set references to our worksheets
    Set wsAllScenarios = ThisWorkbook.Worksheets("AllScenarios")
    Set wsBuilder = ThisWorkbook.Worksheets("ScenarioBuilder")
    
    ' Only run if the change is in Column D (Operation) starting from Row 3
    If Target.Column = 4 And Target.Row >= 3 And Target.Cells.Count = 1 Then
        currentRow = Target.Row
        selectedOperation = Target.Value
        
        ' Handle first step (Row 3) - no previous status to check
        If currentRow = 3 Then
            lastRowAll = wsAllScenarios.Cells(wsAllScenarios.Rows.Count, "E").End(xlUp).Row
            ' Find the first matching operation in AllScenarios
            For i = 2 To lastRowAll ' Assume Row 1 is header in AllScenarios
                If wsAllScenarios.Cells(i, "E").Value = selectedOperation Then
                    ' Sync data to current row
                    wsBuilder.Cells(currentRow, "E").Value = wsAllScenarios.Cells(i, "F").Value ' Out-status ID
                    wsBuilder.Cells(currentRow, "F").Value = wsAllScenarios.Cells(i, "G").Value ' Out-status
                    wsBuilder.Cells(currentRow, "G").Value = wsAllScenarios.Cells(i, "H").Value ' SLA
                    wsBuilder.Cells(currentRow, "H").Value = wsAllScenarios.Cells(i, "A").Value ' Task ID
                    wsBuilder.Cells(currentRow, "I").Value = wsAllScenarios.Cells(i, "B").Value ' Task Name
                    wsBuilder.Cells(currentRow, "J").Value = wsAllScenarios.Cells(i, "C").Value ' Role
                    Exit For
                End If
            Next i
        Else
            ' Get previous row's Out-status for validation
            prevOutStatus = wsBuilder.Cells(currentRow - 1, "F").Value
            
            ' Find matching Operation AND In-status in AllScenarios
            lastRowAll = wsAllScenarios.Cells(wsAllScenarios.Rows.Count, "E").End(xlUp).Row
            For i = 2 To lastRowAll
                If wsAllScenarios.Cells(i, "E").Value = selectedOperation And _
                   wsAllScenarios.Cells(i, "D").Value = prevOutStatus Then
                    ' Sync data to current row
                    wsBuilder.Cells(currentRow, "E").Value = wsAllScenarios.Cells(i, "F").Value
                    wsBuilder.Cells(currentRow, "F").Value = wsAllScenarios.Cells(i, "G").Value
                    wsBuilder.Cells(currentRow, "G").Value = wsAllScenarios.Cells(i, "H").Value
                    wsBuilder.Cells(currentRow, "H").Value = wsAllScenarios.Cells(i, "A").Value
                    wsBuilder.Cells(currentRow, "I").Value = wsAllScenarios.Cells(i, "B").Value
                    wsBuilder.Cells(currentRow, "J").Value = wsAllScenarios.Cells(i, "C").Value
                    Exit For
                End If
            Next i
        End If
        
        ' Generate dynamic dropdown for the next row
        lastRowBuilder = wsBuilder.Cells(wsBuilder.Rows.Count, "D").End(xlUp).Row
        Set uniqueOperations = New Collection
        
        ' Get current row's Out-status to find next possible operations
        currentOutStatus = wsBuilder.Cells(currentRow, "F").Value
        
        ' Collect unique operations from AllScenarios where In-status matches current Out-status
        On Error Resume Next ' Ignore duplicate entries in collection
        lastRowAll = wsAllScenarios.Cells(wsAllScenarios.Rows.Count, "D").End(xlUp).Row
        For i = 2 To lastRowAll
            If wsAllScenarios.Cells(i, "D").Value = currentOutStatus Then
                uniqueOperations.Add wsAllScenarios.Cells(i, "E").Value, Key:=CStr(wsAllScenarios.Cells(i, "E").Value)
            End If
        Next i
        On Error GoTo 0
        
        ' Clear existing validation in next row's Column D
        wsBuilder.Cells(lastRowBuilder + 1, "D").Validation.Delete
        
        ' Add new data validation if there are operations to show
        If uniqueOperations.Count > 0 Then
            Dim opList As String
            opList = ""
            For Each op In uniqueOperations
                opList = opList & op & ","
            Next op
            opList = Left(opList, Len(opList) - 1) ' Remove trailing comma
            
            With wsBuilder.Cells(lastRowBuilder + 1, "D").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=opList
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .ShowError = True
            End With
        End If
    End If
End Sub

Step 3: Initial Setup for the First Dropdown #

For the first row (Row 3) in ScenarioBuilder Column D, set up an initial dropdown with all unique operations from AllScenarios:

1. Select cell D3 in ScenarioBuilder
2. Go to Data > Data Validation > List
3. For the source, use this formula (works in Excel 365/2021):
   =UNIQUE(AllScenarios!E:E)
   If you have an older Excel version, use a helper column in AllScenarios to get unique operations, or adjust the VBA to generate the initial dropdown automatically.

How It Works #

• When you select an operation in Column D, the VBA checks if it’s the first step (no previous status) or a subsequent step (matches the previous row’s Out-status to AllScenarios In-status)
• It automatically syncs all related data (Out-status ID, SLA, Task details, etc.) to the current row
• After syncing, it generates a unique dropdown of possible operations for the next row based on the current step’s Out-status
• You can repeat this process to build out your full scenario step-by-step

内容的提问来源于stack exchange，提问作者Mohanad Elsayed