msg box w/ dynamic text

[Loreng]

i have a macro that runs every time external data is pulled into a spreasheet and would like a message box to pop up telling the user which of the multiple different control totals do not tie out. my current code works, but it makes a seperate message box each total that does not tie.....is there a way to include all the checks that do not match in one msg box.... is "check1, check2 and check3 are not equal" instead of "check1 is not equal", "check2 is not equal" and "check3 is not equal"

here is my current code"
Sub check()

If check0 = truecheck Then
MsgBox "control totals tie out"
ElseIf check1 <> trucheck Then
MsgBox "check 1 is not equal"
ElseIf check2 <> trucheck Then
MsgBox "check 2 is not equal"
ElseIf check3 <> trucheck Then
MsgBox "check 3 is not equal"
End If
End Sub

Also, cell truechek is just 1=1 to get a value of TRUE....how do I just use TRUE in vba?

Thanks -Loren

 

[Tazguy37]

Sounds like this might be of some use to you:

Sub check()
Dim Str As String
Dim check0, check1, check2, check3 As Boolean

check0 = False  'I've set these myself just for testing purposes
check1 = True
check2 = False
check3 = True
Str = ""

    If check0 Then
        Str = "control totals tie out"
    ElseIf Not check1 Then
        Str = Str & " check 1 is not equal"
    ElseIf Not check2 Then
        Str = Str & " check 2 is not equal"
    ElseIf Not check3 Then
        Str = Str & " check 3 is not equal"
    End If
    MsgBox Str
    
End Sub

Also, there's no need to set a variable to True, and then check against it; just use True. Or, you can do as I've shown above.

    ElseIf Not check3 Then
        Str = Str & " check 3 is not equal"
    End If

This essentially means the same as:

ElseIf check3=False Then
...

Just a shorter way of doing it. Hope that helps!

 

[chiello]

Sorry Tazguy, but I think your code could fail. Infact

if.. then
'actions
elseif .. then
'actions
...
elseif ... then
'actions
end if

just executes actions relevant to the first True condition, while, according to Loreng request, check0 could be True and (or) check1, check2 and check3 could be False.

I propose the following code:

Sub check()
Dim StrTot As String, Str0 As String, Str123 As String
Dim check0, check1, check2, check3 As Boolean
Dim CountCheckFail As Integer

check0 = fase 'I've set these myself just for testing purposes
check1 = False
check2 = True
check3 = True
CountCheckFail = 0
StrTot = ""
Str0 = ""
Str123 = ""

If check0 Then
Str0 = "control totals tie out"
End If
If Not check1 Then
Str123 = "check 1 is not equal"
CountCheckFail = CountCheckFail + 1
End If
If Not check2 Then
If CountCheckFail = 0 Then
Str123 = "check 2 is not equal"
Else
Str123 = "check 1 and check 2 are not equal"
End If
CountCheckFail = CountCheckFail + 2
End If
If Not check3 Then
Select Case CountCheckFail
Case 0
Str123 = "check 3 is not equal"
Case 1
Str123 = "check 1 and check 3 are not equal"
Case 2
Str123 = "check 2 and check 3 are not equal"
Case 3
Str123 = "check 1, check 2 and check 3 are not equal"
End Select
End If
If Str0 <> "" And Str123 <> "" Then
StrTot = Str0 & "; " & Str123
ElseIf Str0 <> "" Then
StrTot = Str0
ElseIf Str123 <> "" Then
StrTot = Str123
End If
If StrTot <> "" Then
MsgBox StrTot
End If
End Sub

Ciao Tazguy
Ciao Loreng

 

[Loreng]

TazGuy, Chiello - Thanks for your help.

I have modified my code based on your suggestions. And have it working the way I want it to...EXCEPT when i take out the definetions of the boolean variables....ie:

check0 = False
check1 = False
check2 = True
check3 = False

Check0 -3 are named cells in my worksheet. When I run the macro with the above in it works perfectly. When I take them out, a the msg box says "check: check1, check2, check3" even though all cells = true.

What am I doing wrong?

Sub check()
Dim Str As String
Dim check0, check1, check2, check3 As Boolean
Dim CountCheck As Integer
Str = " "
CountCheck = 0
'I've set these myself just for testing purposes
'check0 = False
'check1 = False
'check2 = True
'check3 = False
'
If check0 Then
Str = "control totals tie out"
MsgBox Str
Exit Sub
End If

Str = "CHECK:"
If Not check1 Then
Str = Str & " check1"
CountCheck = CountCheck + 1
End If
If Not check2 Then
If CountCheck > 0 Then
Str = Str & ","
End If
Str = Str & " check2"
CountCheck = CountCheck + 1
End If
If Not check3 Then
If CountCheck > 0 Then
Str = Str & ","
End If
Str = Str & " check3"
CountCheck = CountCheck + 1
End If
MsgBox Str

End Sub

 

[chiello]

...
Check0 -3 are named cells in my worksheet. When I run the macro with the above in it works perfectly. When I take them out, a the msg box says "check: check1, check2, check3" even though all cells = true.

What am I doing wrong?

Try this:

delete "Dim check0, check1, check2, check3 As Boolean"

Replace:
check0 with Range("check0")
check1 with Range("check1")
check2 with Range("check2")
check3 with Range("check3")

Ciao

 

[Loreng]

Thanks Chiello...that's perfect!