Limiting a conditional message box to pop up only once?

[dougmarkham]

Hi Folks,

I have some VBA code that highlights cells which exceed a certain number of characters 'Red'--- until the cell character count drops below the condition, then the cell becomes un-highlighted.

At the end of the VBA I put a message box to inform the user their cells exceed character limits. This message box currently pops up every time there is a change to the cell contents, until all the cells referred to in the code meet the conditions.

Private Sub Worksheet_Change(ByVal Target As Range)Dim c As Range
Dim LR As Integer
Dim numProbs As Long
Dim sht As Worksheet


Set sht = Worksheets("DX import template")


numProbs = 0
LR = sht.Cells(Rows.Count, "A").End(xlUp).Row
For Each c In Range("A2:A" & LR)
    If Len(c.Value) > 12 Then
        c.Interior.Color = vbRed
        numProbs = numProbs + 1
    Else
      c.Interior.Color = xlNone
    End If
Next
For Each c In Range("B2:B" & LR)
    If Len(c.Value) > 20 Then
        c.Interior.Color = vbRed
        numProbs = numProbs + 1
    Else
      c.Interior.Color = xlNone
    End If
Next
For Each c In Range("C2:C" & LR)
    If Len(c.Value) > 50 Then
        c.Interior.Color = vbRed
        numProbs = numProbs + 1
    Else
      c.Interior.Color = xlNone
    End If

[COLOR=#0000cd][B]If numProbs > 0 Then[/B][/COLOR][COLOR=#0000cd][B]    MsgBox "Character limits - Columns: A (12), B (20), C (50) see " & numProbs & " red cells"[/B][/COLOR]

End If


End Sub

What I want this code to do is to activate the pop-up box only once if any of the cells contain more than the permitted characters, so that it's not necessary to keep closing the pop-up box after each amendment. I googled this without success.

Would anybody be able to help me amend this code please?

Kind regards,

Doug.

 

[dmt32]

Hi,
try moving your msgbox outside the for next loop

For Each c In Range("C2:C" & LR)
    If Len(c.Value) > 50 Then
        c.Interior.Color = vbRed
        numProbs = numProbs + 1
    Else
      c.Interior.Color = xlNone
    End If


End If
Next


If numProbs > 0 Then MsgBox "Character limits - Columns: A (12), B (20), C (50) see " & numProbs & " red cells"

Dave

 

[dougmarkham]

Hi Dave,

Thanks for answering.

If I move the message box code outside as shown in your code window, I get the following error:

Compile error: End If without block If

I tried this though based on what you said:

For Each c In Range("P2:P" & LR)    If Len(c.Value) > 10 Then
        c.Interior.Color = vbRed
        numProbs = numProbs + 1
    Else
      c.Interior.Color = xlNone
    End If
Next
If numProbs > 0 Then
    MsgBox "Character limits - Columns: A (12), B (20), C to G (50), H & I (20), J & K (8), L (12), M (5), N(3), O & P (10) see " & numProbs & " red cells"
End If


End Sub

This gave me the same result: every red cell I altered to below the character limit, it went un-highlighted but then the msgbox popped up.

Kind regards,

Doug

 

[dmt32]

First, sorry about that my bad, forgot to delete the Endif

If you are just testing for character length the try using the Text property of Range and see if this helps

If Len(c.Text) > 10 Then

Dave

 

[dougmarkham]

First, sorry about that my bad, forgot to delete the Endif

If you are just testing for character length the try using the Text property of Range and see if this helps

If Len(c.Text) > 10 Then

Dave

Hi Dave,

This works better, thanks!

Kind regards,

Doug.