vba - 为每个事件发生在一个 msgbox 中组合多个消息框

Question

我试图在我的 BeforeSave 事件中输出特定的错误消息。这是示例消息框:http://prntscr.com/jtlxt2 .
对于每个用例，必须用特定测试用例中定义的错误消息替换该部分。如果有多个问题，所有错误消息都应打印在一个消息框中。以下是可能的错误消息:

1.blockTemplate的ID缺失

2.必须定义参数“ID”

3.B2单元格不允许为空

4.Cell A2 包含无效值:“Ids”

5.Font Size 必须是 6 到 72 之间的整数

6.Paragraph Spacing Before 必须是 6 到 72 之间的整数
字体大小必须是 6 到 72 之间的整数

表“列变体”:

7.Variant IDs QINTRO_VAR1, QINTRO_VAR2 与全局ID QUINTRO不兼容

8.C6单元格不能为空。要为此值定义 null，请使用减号 (-)。

这是我到目前为止编写的代码:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.EnableEvents = False
Dim cell As Range
Dim j As String
Dim i As Integer
Dim cellVal As Integer
Dim cellVal2 As Integer
Dim sCellVal As String
Dim a As Variant
Dim Target As Range
Dim arr As Range
Dim rngcheck As Range
Dim rngcheck2 As Range

sCellVal = Range("A2").Value
cellVal = Range("B3").Value
cellVal2 = Range("B4").Value

   If Not IsNumeric(cellVal) Then
   MsgBox "Only numeric values allowed."
   End If

  'If Sheets("General Info").Range("A2").Value = "" Then
  'Cancel = True
  'MsgBox "Save cancelled"
  'End If

  If Not sCellVal = "ID" Then
      Cancel = True
      MsgBox "The Parameter “ID” must be defined"
  End If

  If sCellVal = "" Then
      Cancel = True
  MsgBox "Missing ID for the blockTemplate"
  End If

  If sCellVal = "IDs" Then
      'Cancel = True
       MsgBox "Cell A2 contains an invalid value: “Ids”"
  End If

  If Not cellVal = (6 < 72) Then
      MsgBox "Font Size must be an integer from 6 till 72"
  End If

  If Not cellVal2 = (6 < 72) Then
      MsgBox "Paragraph Spacing Before must be an integer from 6 till 72"
  End If

   'Set arr = Range("C6:C7")
    'If the columns is the eighth
    'For Each a In arr
     'ActiveSheet.Range("C6:C7").Select
    'If Target.Column = 2 And (Target.Row > 5 And Target.Row < 8) Then


          Set rngcheck2 = Range("C6:C7")

       For Each cell In rngcheck2
    If IsEmpty(cell) Then


          MsgBox (" The cell" + Target.Address(0, 0)) + "is not allowed to be empty. To define null for this value use the minus sign (-)."
     'The Cell C6 is not allowed to be empty. To define null for this value use the minus sign (-).
    End If
    Next cell



        'Next a:
          MsgBox (" The Variant IDs QINTRO_VAR1, QINTRO_VAR2 are not compatible with the global ID QUINTRO")

        Set rngcheck = Range("B2:B4")

         i = 0

          For Each cell In rngcheck


        If IsEmpty(cell) Then

           i = i + 1

          j = j & cell.Address & vbNewLine

        End If

          Next cell

         If i = 0 Then Exit Sub

              MsgBox "Sorry, you must enter a value in: " & vbNewLine & j

         Application.EnableEvents = True

End Sub

Answer 1

我会构建一条消息，然后只输出一次。喜欢:

Sub whatever()
    Dim mess As String
    mess = ""

  If Not sCellVal = "ID" Then
      Cancel = True
      mess = mess & vbCrLf & "The Parameter “ID” must be defined"
  End If

  If sCellVal = "" Then
      Cancel = True
      mess = mess & vbCrLf & "Missing ID for the blockTemplate"
  End If

  ' more code

  If mess <> "" Then MsgBox mess
End Sub