VBA

[LBee]

I have a small VBA script that, when I press button, checks if values in different cells are valid.
If not, it displays a messagebox telling which cell is not correct, otherwise is displays a messagebox saying "Everything looks fine"
It does work, but it checks one cell at a time - if 3 cells are wrong I have to press the button, fix first cell, press it again, fix second cell ...

What I like it to do, was to check all cells, and then display only one messagebox saying e.g. :

A1 is empty
A7 does not contain 5 digits
D2 does not contain 7 digits

Is that possible?

This is the script I'm currently using

Sub Button1_Click()
    
    If IsEmpty(Range("A1")) = True Then
        MsgBox "A1 is empty"
    ElseIf IsNumeric(Range("B4")) = False Then
        MsgBox "B4 is not a number"
    ElseIf Len(Range("A7")) <> 5 Then
        MsgBox "A7 does not contain 5 digits"
    ElseIf Len(Range("D2")) <> 7 Then
        MsgBox "D2 does not contain 7 digits"
    Else
        MsgBox "Everything looks fine "
    End If

End Sub

 

[Phuoc]

Try this

Sub Button1_Click()
    Dim tmp As String
    If IsEmpty(Range("A1")) = True Then tmp = "A1 is empty"        
    If IsNumeric(Range("B4")) = False Or Range("B4") = "" Then tmp = tmp & vbNewLine & "B4 is not a number"      
    If Len(Range("A7")) <> 5 Then tmp = tmp & vbNewLine & "A7 does not contain 5 digits"
    If Len(Range("D2")) <> 7 Then tmp = tmp & vbNewLine & "D2 does not contain 7 digits"      
    If Len(tmp) > 0 Then
        If AscW(tmp) = 13 Then tmp = Mid(tmp, 3)
        MsgBox tmp
    Else
        MsgBox "Everything looks fine "
    End If

End Sub

 

[Trebor76]

Slightly different way:

Option Explicit
Sub Macro1()

    Dim strMsg As String

    If IsEmpty(Range("A1")) = True Then
        strMsg = "A1 is empty"
    End If
    If IsNumeric(Range("B4")) = False Then
        strMsg = IIf(Len(strMsg) = 0, "B4 is not a number", strMsg & vbNewLine & "B4 is not a number")
    End If
    If Len(Range("A7")) <> 5 Then
        strMsg = IIf(Len(strMsg) = 0, "A7 does not contain 5 digits", strMsg & vbNewLine & "A7 does not contain 5 digits")
    End If
    If Len(Range("D2")) <> 7 Then
        strMsg = IIf(Len(strMsg) = 0, "D2 does not contain 7 digits", strMsg & vbNewLine & "D2 does not contain 7 digits")
    End If
    If Len(strMsg) = 0 Then
        strMsg = "Everything looks fine"
    End If
    
    MsgBox strMsg

End Sub

 

[LBee]

Both of you, thank you very much - and sorry about the thread title, I was so focused on the description, that I didn't notice something was missing in the title

I tested both suggestion, and both are working
Regarding the solution Trebor76 suggested - I had to remove "Option Explicit" to make it work, what should this line do?

 

[LBee]

@Phuoc I'm trying to understand what I'm doing, but I cant figure out this part "If AscW(tmp) = 13 Then tmp = Mid(tmp, 3)"
Could you explain that part?

 

[bebo021999]

if string was " " (space vbNewline on VBA or Alt-Enter in worksheet input), AscW(string) = 13
If the first 3 IFs failed, it release 3 spaces, like this: " D7 does not..."
Since trim function does not work for that kind of space, using MID(string,3) to get partial string counted from 3th position.

 

[Trebor76]

I had to remove "Option Explicit" to make it work, what should this line do?

Option Explicit forces you to declare all your variables. If you don't use it any undeclared variables will be defined as variants - the most expensive type of variable which for large procedures can have an impact.

You must have at least one undeclared variable in your code. It is good practice to always use Option Explicit.

Regards,

Robert

 

[LBee]

Thanks for the explanation @Trebor76 and @bebo021999
I think I have to read more about this Option Explicit to fully understand it, its a "little" beyond my level, but now I know what to look for.

Happy new year

Regards
/L