Line breaks in MsgBox concatenated variables
Sub EF()
Dim sStr As String
Dim rng As Range
Dim rng1 as Range
Dim j As Long, i As Long
j = 0
Set rng = Range("A1").Resize(8, 8)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng1 Is Nothing Then
sStr = rng1.Address(0, 0)
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 3 Then
Mid(sStr, i, 1) = Chr(10)
j = 0
End If
End If
Next
MsgBox ("The blank cells a " & vbCrLf & _
vbCrLf & sStr & vbCrLf & vbCrLf & _
"You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
End If
End Sub
--
Regards,
Tom Ogilvy
"Sandy" wrote in message
...
Hello -
I have a msgbox that returns a concatenation of cell addresses that are
blank. My problem is there could be as many as 64 cell addresses
returned,
which would make the message box extend completely across the screen.
sStr
is the cell address. Here's the code:
MsgBox ("The blank cells a " & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
Is there any way I can get it to display three sStr and then do a vbCrLf,
then another three, etc.?
Any help will be greatly appreciated!
--
Sandy
|