CODEWITHSUNDEEP
vbaexcel
Max
Max

Reputation: 449

VBA - Type mismatch on declaration of Excel.Range

I am trying to declare a range of cells as an Excel.Range variable in the BeforeSave() event of my excel workbook. The background is, that the values in this range are mandatory inputs and I want to validate that they are all filled on saving.

If I want to execute the function I get the error message

Runtime Error "13": Type Mismatch

Here's the code I tried.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim r1 As Range
    Set r1 = Range("G11:G14")

        If Cells(10, 1).Value = "" Then
            MsgBox "Cell requires user input", vbInformation, "Please filled up the mandatory cells"
            Cancel = True
            Exit Sub
        ElseIf r1.Value = "" Then // runtime error "13": Type Mismatch
            MsgBox "Please make sure you had filled in all the Questionnire Answers.", vbInformation, "Missing Answer"
            Cancel = True
            Exit Sub
        End If
        Cancel = False
End Sub

I am relatively new to VBA so please feel free to point out my mistake. Thanks in advance!

Upvotes: 2

Views: 2732

Answers (2)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

You will get this error:

Runtime Error "13": Type Mismatch

Because r1 is defined as a Range of multiple cells and you cannot check if a multiple-cell Range is simply empty string. You need to check each cell in the range.

Try this code - it is a Function that checks to see if any cell, in a group of cells, is "" and returns True if that is so:

Option Explicit

Function TestMultipleCellsAnyAreEmpty(rng As Range) As Boolean

    Dim rngCell As Range
    Dim blnAnyRangeIsEmpty

    blnAnyRangeIsEmpty = False

    For Each rngCell In rng
        If rngCell.Value = "" Then
            blnRangeIsEmpty = True
            Exit For
        End If
    Next rngCell

    TestMultipleCellsAreEmpty = blnRangeIsEmpty

End Function

Combining this technique with your workbook event you can have this complete code:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim r1 As Range
    Set r1 = ThisWorkbook.Worksheets("YOUR_SHEET").Range("G11:G14") '<-- specify the worksheet

    If ThisWorkbook.Worksheets("YOUR_SHEET").Cells(10, 1).Value = "" Then '<-- specify the worksheet
        MsgBox "Cell requires user input", vbInformation, "Please filled up the mandatory cells"
        Cancel = True
        Exit Sub
    ElseIf TestMultipleCellsAnyAreEmpty(r1) Then
        MsgBox "Please make sure you had filled in all the Questionnire Answers.", vbInformation, "Missing Answer"
        Cancel = True
        Exit Sub
    End If
    Cancel = False

End Sub

Function TestMultipleCellsAnyAreEmpty(rng As Range) As Boolean

    Dim rngCell As Range
    Dim blnAnyRangeIsEmpty

    blnAnyRangeIsEmpty = False

    For Each rngCell In rng
        If rngCell.Value = "" Then
            blnRangeIsEmpty = True
            Exit For
        End If
    Next rngCell

    TestMultipleCellsAreEmpty = blnRangeIsEmpty

End Function

Upvotes: 1

kschindl
kschindl

Reputation: 233

I suspect it's because you need to qualify your range definition to say Set r1 = ThisWorkbook.Range("G11:G14"). Also, I believe that the .Value property of a multi-cell range will return the value in the top leftmost cell.

Upvotes: 1

Related Questions