Excel：单元格输入值匹配特定内容时弹窗确认并填充单元格求助

Hey there! Let’s get that Excel macro working for you. You’re on the right track with vbYesNo—the issue is probably just in how you’re triggering the popup and handling the response. Let’s break this down step by step with a working example.

We’ll use the Worksheet_Change event (this triggers automatically when a cell’s value changes) to make this work seamlessly. Here’s exactly what to do:

1. Open your workbook, go to the worksheet where you want this functionality, right-click the sheet tab (like "Sheet1") and select View Code.
2. Paste this code into the VBA editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Customize these to match your needs
    Dim inputCell As Range: Set inputCell = Me.Range("A1") ' Cell where user inputs text
    Dim fillCell As Range: Set fillCell = Me.Range("B1") ' Cell to fill if "Yes" is clicked
    Dim triggerText As String: triggerText = "Approved" ' The value that triggers the popup
    Dim fillValue As String: fillValue = "Confirmed" ' Value to put in fillCell
    
    ' Only run code if the changed cell is our input cell
    If Not Intersect(Target, inputCell) Is Nothing Then
        ' Check if the input matches our trigger text
        If Target.Value = triggerText Then
            ' Show the Yes/No popup
            Dim userChoice As VbMsgBoxResult
            userChoice = MsgBox("Do you want to mark this as confirmed?", vbYesNo + vbQuestion, "Confirm Action")
            
            ' Handle the user's selection
            Select Case userChoice
                Case vbYes
                    fillCell.Value = fillValue ' Fill the target cell
                Case vbNo
                    ' Do nothing—just close the popup
            End Select
        End If
    End If
End Sub

Key Customizations You Need to Make #

• Change inputCell to the actual cell where users enter text (e.g., Me.Range("C5")).
• Update fillCell to the cell you want to populate when "Yes" is clicked.
• Set triggerText to the specific value that should trigger the popup (e.g., "Submit", "Complete").
• Adjust fillValue to whatever content you want in the fill cell (can be text, a number, or even a formula like "=TODAY()").

Troubleshooting Common Issues #

• Macro not running? Make sure you saved your workbook as a .xlsm (macro-enabled) file, and that macros are enabled in Excel’s Trust Center settings.
• Popup triggers for wrong cells? Double-check the inputCell reference—Intersect(Target, inputCell) ensures only the specified cell triggers the code.
• Case sensitivity issues? If you want to match "approved" or "APPROVED" too, replace Target.Value = triggerText with UCase(Target.Value) = UCase(triggerText).

That should do it! Test by entering your trigger text into the input cell—you’ll see the popup, and clicking "Yes" will fill your target cell right away.

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