excel - 当单元格值不等于“X”时，是否有打开 msgbox 的 vba 代码？

Question

我不精通vba，所以我认为这是最好的提问地方。我的目标是在特定单元格不等于“X”值时出现一个 msgbox。这是我下面的代码

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myCell As Range

For Each myCell In Range("G4:G160")
    If myCell.Value <> 17521 Then
        MsgBox "INCORRECT SKU RECHECK PALLET AND INFORM SUPERVISOR"
        Exit Sub
    End If
Next myCell
End Sub

单元格值取自另一个单元格，因此无需手动输入。当我运行此宏时，每次更改单元格或单击时它都会出现。数字正确时也会出现。我只需要这个 msgbox 仅在它不等于该值时出现。

我还希望消息是红色的。如果这很容易，请让我知道该怎么做或改变。对我来说最大的学习曲线，因为我总是避免使用 vba 代码。

编辑：请看下面的链接。右边的 1,2 和 3 的条形码是正确的，可以扫描到 B4 等等，错误的 4 和 5。我希望 msgbox 或 userform 能直观地显示错误的。如果输入正确，则不会弹出。

在此处输入图像描述 https://1drv.ms/x/s!AvRcvV5GEpBXnRIrzT2xDTv0iTQh?e=tV3LeN

标签： excelvbarangemsgbox

Answer 1

Your code seems to be right. Once you select another cell in your target sheet, this code runs and will never show the msgbox if all values in the range "G4:G160" are equal to 17521. If it appears, there is certainly at least one value <>17521. I recommend to use the Change Event instead:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Range

    For Each myCell In Range("G4:G160")

        If (Not IsEmpty(myCell)) And myCell.Value <> 17521 And myCell.Value <> "" Then

            MsgBox "INCORRECT SKU RECHECK PALLET AND INFORM SUPERVISOR", vbCritical

            Exit Sub
        End If

    Next myCell
End Sub

About the red color of the msgbox, you cannot change the color of a msgbox. You have two options:

1. Instead of red color, show a Critical icon as:

MsgBox "INCORRECT SKU RECHECK PALLET AND INFORM SUPERVISOR", vbCritical

In this case, you'll see something like this:

2. Create a UserForm and adjust its font color and then show it instead of MsgBox. If you want to use the second option, please let me know and then help you create it.