none
Run-Time Error '13' type mismatch RRS feed

  • Question

  • Hey all,

    I am getting a Run-Time error '13' on my following code and i was hoping you'd have the solution to the error.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'If the target cell is clear
         'If Target.Value = "" Then
         

            'Then change the background to the specified color
            'Target.Value = "Not Complete"
            
        If Target.Value = "Not Complete" Then
            
            'But if the target cell is already the specified color
            Target.Value = "In Progress"
            
            'Then change the background to the specified color
        ElseIf Target.Value = "In Progress" Then
            
            'But if the target cell is already the specified color
            Target.Value = "Complete"
            
            'Then change the background to the specified color
        ElseIf Target.Value = "Complete" Then
            
            Target.Value = "Not Complete"
            
            
            
        

        End If
    End Sub

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

        Cancel = True
        Worksheet_SelectionChange Target

    End Sub

    Thanks!

    Thursday, August 11, 2016 4:42 PM

Answers

  • The code works correctly as long as the user selects only a single cell. If the user selects a multi-cell range, Target.Value is not a single value but an array of values, so a line such as

     If Target.Value = "Not Complete" Then

    will cause the error that you report.

    If you want ALL cells in the selected range to change:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim cel As Range
        For Each cel In Target
            If cel.Value = "Not Complete" Then
                cel.Value = "In Progress"
            ElseIf cel.Value = "In Progress" Then
                cel.Value = "Complete"
            ElseIf cel.Value = "Complete" Then
                cel.Value = "Not Complete"
            End If
        Next cel
    End Sub

    But if you only want the active cell within the selection to change:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If ActiveCell.Value = "Not Complete" Then
            ActiveCell.Value = "In Progress"
        ElseIf ActiveCell.Value = "In Progress" Then
            ActiveCell.Value = "Complete"
        ElseIf ActiveCell.Value = "Complete" Then
            ActiveCell.Value = "Not Complete"
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, August 11, 2016 6:30 PM

All replies

  • The code works correctly as long as the user selects only a single cell. If the user selects a multi-cell range, Target.Value is not a single value but an array of values, so a line such as

     If Target.Value = "Not Complete" Then

    will cause the error that you report.

    If you want ALL cells in the selected range to change:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim cel As Range
        For Each cel In Target
            If cel.Value = "Not Complete" Then
                cel.Value = "In Progress"
            ElseIf cel.Value = "In Progress" Then
                cel.Value = "Complete"
            ElseIf cel.Value = "Complete" Then
                cel.Value = "Not Complete"
            End If
        Next cel
    End Sub

    But if you only want the active cell within the selection to change:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If ActiveCell.Value = "Not Complete" Then
            ActiveCell.Value = "In Progress"
        ElseIf ActiveCell.Value = "In Progress" Then
            ActiveCell.Value = "Complete"
        ElseIf ActiveCell.Value = "Complete" Then
            ActiveCell.Value = "Not Complete"
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, August 11, 2016 6:30 PM
  • the Active Cell Code worked Perfectly! thank you!!!!!
    Friday, August 12, 2016 10:38 AM