Auto populating MsgBox

Question

I was wondering if anybody could help me turn the below into a msg box. Basically i want it to first check whether any of the below range's say CHECK and if they do a msgbox will appear explaining which rule has been breached. I have had an attempt at creating the variable but not really sure how to put this into practice in a msgbox.

Sub ErrorMsgBox()

Dim Error1 As String
    If Range("DaisyFreshRule").Value = "CHECK" Then
    Error1 = "Daisy Fresh Rule"
Else
End If

Dim Error2 As String
    If Range("MigrationRule").Value = "CHECK" Then
    Error2 = "Migration Rule"
Else
End If

Dim Error3 As String
    If Range("ServiceCreditRule").Value = "CHECK" Then
    Error3 = "Service Credit Rule"
Else
End If

End Sub

score 6 · Accepted Answer · edited May 26 '17 at 15:23

6

Sub ErrorMsgBox()

  Dim Error As String: Error=""

  If Range("DaisyFreshRule").Value = "CHECK" Then
    Error = vbNewLine & "Daisy Fresh Rule"
  End If

  If Range("MigrationRule").Value = "CHECK" Then
    Error = Error & vbNewLine & "Migration Rule"
  End If

  If Range("ServiceCreditRule").Value = "CHECK" Then
    Error = Error & vbNewLine & "Service Credit Rule"
  End If

  If Error <> "" Then
    MsgBox "The following checks have been detected:" & Error
  End If

End Sub

edited May 26 '17 at 15:23

Mathieu Guindon

69,817
8
107
235

answered May 26 '17 at 15:05

Amorpheuses

1,403
1
9
13

++ Ha! you beat me to it :D – Siddharth Rout May 26 '17 at 15:07
I don't think '\n' will evaluate to a new line in vba – Tom May 26 '17 at 15:09
Yes it's been changed to vbLf. Thanks @Tom – Amorpheuses May 26 '17 at 15:21

Tom · Answer 2 · 2017-05-26T15:23:26.237

3

Give this a try

Sub ErrorMsgBox()
    Dim Err As String

    If UCase(Range("DaisyFreshRule").Value) = "CHECK" Then Err = Err & "Daisy Fresh Rule" & vbNewLine
    If UCase(Range("MigrationRule").Value) = "CHECK" Then Err = Err & "Migration Rule" & vbNewLine
    If UCase(Range("ServiceCreditRule").Value) = "CHECK" Then Err = Err & "Service Credit Rule" & vbNewLine

    If Not Err = vbNullString Then MsgBox prompt:=Err, Buttons:=vbCritical
End Sub

edited May 26 '17 at 15:23

answered May 26 '17 at 15:03

Tom

9,725
3
31
48

If there is no error, you will still get a msgbox :) – Siddharth Rout May 26 '17 at 15:09
++ nice :) Time for me to almost hit the sack :D – Siddharth Rout May 26 '17 at 15:10
Note, `Error` local variable is shadowing the `VBA.Conversion.Error` standard library function. – Mathieu Guindon May 26 '17 at 15:22
I too thought that so i built built it on my button so that only if any of the rules are broken then the msgbox then appears. Thanks for the help works great now – Peter Mogford May 26 '17 at 15:22
Just changed it so it doesn't overlap – Tom May 26 '17 at 15:23
Also @PeterMogford, Just clicked on your profile - your MD is my old boss - small world – Tom May 26 '17 at 15:24
very small @Tom – Peter Mogford May 26 '17 at 15:42

Auto populating MsgBox

2 Answers2