CODEWITHSUNDEEP
vbaexcel
Hris
Hris

Reputation: 153

Combining several message boxes in one msgbox for each event occurrence

I am trying to output specific error messages in my BeforeSave event. Here is the example message box: http://prntscr.com/jtlxt2. For every use case the part has to be replaced by the defined error message from the specific test case. If there are multiple issues, all error messages should be printed in one message box. Here are the possible error messages:

1.Missing ID for the blockTemplate

2.The Parameter “ID” must be defined

3.The cell B2 is not allowed to be empty

4.Cell A2 contains an invalid value: “Ids”

5.Font Size must be an integer from 6 till 72

6.Paragraph Spacing Before must be an integer from 6 till 72 Font Size must be an integer from 6 till 72

Table "Column Variants":

7.The Variant IDs QINTRO_VAR1, QINTRO_VAR2 are not compatible with the global ID QUINTRO

8.The Cell C6 is not allowed to be empty. To define null for this value use the minus sign (-).

Here is the code I`ve written so far:

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

Upvotes: 1

Views: 1808

Answers (3)

AndyW
AndyW

Reputation: 430

Create a String called errorString.

Replace your existing "MsgBox " with "errorString=errorString & vbCrLf"

At the end of your routine check to see if there has been any errors (errorString contains something ) and then just msgbx errorString

If len(errorString)>0 Then
    errorString = "Please correct the following Errors before continuing" & errorString (or whatever)
endif

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

I would build a message and then output it only once. Like:

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

Upvotes: 2

Cyril
Cyril

Reputation: 6829

Okay... this is not pretty, and it's partly because VBA wants to go line by line... so each error message needs to have its own block, such as:

Dim as as string, b as string, c as string
If sCellVal = "ID" Then a = "The Parameter “ID” must be defined."
If sCellVal = "" Then b = "  Missing ID for the blockTemplate."
If sCellVal = "IDs" Then c = "  Cell A2 contains an invalid value: “Ids.”"

MsgBox a & b & c 'Note that I put 2 spaces in front of the text above

You will want to group actions that use Cancel = True into one single grouping, and the non Cancel = True blocks into their own grouping. I would recommend the Cancel = True block appear second, so you can collect all possible error messages.

Upvotes: 0

Related Questions