I need to show a msgbox saying "Please select a maximum of 8 data." when the user selected more than 8 cells in the worksheet

Question

So the maximum allowable data to be copied in another worksheet is 8. Everytime the user chooses more than 8 data, an error will occur. How can I show a my own msgbox instead of the default msgbox provided by VBA.

I am completely new to VBA.

This is my code. It works but I think it is applicable to all errors the user might encounter.

Sub CopySelectedCells()

    On Error GoTo EncounteredError

        Worksheets("3inch_OD7133KS ").Activate
        Selection.Resize(, 4).Copy Destination:=Worksheets("Form").Range("b7")
        Selection.Resize(, 4).Copy Destination:=Worksheets("Form").Range("b27")
        Selection.Resize(, 4).Copy Destination:=Worksheets("Form").Range("b47")

    Exit Sub

EncounteredError:

    MsgBox "ERROR OCCURED: Please choose a MAXIMUM of 8 data."


End Sub

Answer 1

On Error GoTo Handler is typically considered bad practice. You should always try to code out anticipated errors when possible for greater reliability. In this instance, we can simply check the size of the .Selection before taking any actions using .Rows.Count and .Columns.Count. We need to limit the sub to only work when this returns a 8 x 1 range.

---

If this is all your sub is doing then this should be fine....

Sub Selections()

If Selection.Rows.Count <> 8 Or Selection.Columns.Count <> 1 Then
    MsgBox "Error Message Here"
    Exit Sub
Else
    Selection.Resize(, 4).Copy
        With Worksheets("3inch_OD7133KS ")
            .Range("B7").PasteSpecial xlPasteValues
            .Range("B27").PasteSpecial xlPasteValues
            .Range("B47").PasteSpecial xlPasteValues
        End With
End If

End Sub

---

If you have more code to add after you have validated the size of the selected range you can nest your test like this....

Sub Selections()

If Selection.Rows.Count <> 8 Or Selection.Columns.Count <> 1 Then
    MsgBox "Error Message Here"
    Exit Sub
End If

Selection.Resize(, 4).Copy
    With Worksheets("Sheet1")
        .Range("B7").PasteSpecial xlPasteValues
        .Range("B27").PasteSpecial xlPasteValues
        .Range("B47").PasteSpecial xlPasteValues
    End With

'More code here....

End Sub