CODEWITHSUNDEEP
vbaexcel
Eem Jee
Eem Jee

Reputation: 1309

Selecting multiple cells

I have this code that check if the attachment size of the attachment is greater than 10MB. Now, if the attachment is greater than 10MB, it displays the file names on a msgbox then I want to select or highlight the cells that has this attachment greater than 10 MB but dunno how to do it.

Here's what I've tried:

Function checkAttSize()

Application.ScreenUpdating = False
Dim attach As Object
Dim attSize() As String
Dim loc() As String
Dim num As Long
Dim rng As Range

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

Set main = ThisWorkbook.Sheets("Main")
lRow = Cells(Rows.count, 15).End(xlUp).Row
efCount = 0
num = 0
With objMail
    If lRow > 22 Then
    On Error GoTo errHandler
        For i = 23 To lRow
            'attach.Add main.Range("O" & i).value
            'totalSize = totalSize +
            If (FileLen(main.Cells(i, "O").value) / 1000000) > 10 Then
                ReDim Preserve attSize(efCount)
                ReDim Preserve loc(num)
                'store file names
                attSize(efCount) = Dir(main.Range("O" & i))
                'store cell address
                loc(num) = i
                efCount = efCount + 1
                num = num + 1
                found = True
            End If
        Next i
    End If
End With

If found = True Then
    MsgBox "Following File(s) Exceeds 10MB Attachment Size Limit:" & vbCrLf & vbCrLf & Join(attSize, vbCrLf) _
    & vbCrLf & vbCrLf & "Please try removing the file(s) and try again.", vbCritical, "File Size Exceed"
'trying to select the cell addresses
    For i = 1 To num
        rng = rng + main.Range("O" & loc(i)).Select ' Ive also tried &
    Next i
    checkAttSize = True
    Exit Function
End If
Exit Function
errHandler:
MsgBox "Unexpected Error Occured.", vbCritical, "Error"
checkAttSize = True
End Function

Thanks for the help.

Upvotes: 4

Views: 129

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149297

No need to select the range. A single miss click by the user take take the focus away from the range. Also using .Select recklessly may cause run time errors. Color them instead.

After this line

If (FileLen(main.Cells(i, "O").value) / 1000000) > 10 Then

Add this line

main.Cells(i, "O").Interior.ColorIndex = 3

The cells now will be colored in red.

And in the end, alert the user with the message

If found = True Then
   MsgBox "File(s) Exceeding 10MB Attachment Size Limit has been colored in red:"
End If

Upvotes: 5

Related Questions