Type Mismatch when deleting data

Question

Can anyone tell me why if I clear contents of a group of cells I get a Type Mismatch and the only way I can get the code working again is to exit Excel and come back in.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Target.HasFormula Then

    Application.EnableEvents = False

    Select Case Target.Column

    Case Is = 2, 3, 5: Target = UCase(Target)

'or strconv

    Case Else

    End Select

    Application.EnableEvents = True

End If

End Sub

Answer 1

It does for me. Are you sure that you copied the code into the worksheet module?

If so, do the following:

Activate the Visual Basic Editor (Alt+F11)

Activate the Immediate window (Ctrl+G)

Type the following line then press Enter:

Application.EnableEvents = True

Answer 2

Thanks for your help. Not sure why it did not work the first time but is working great now.

Answer 3

It does not seem to capitalize the text that is typed into the cell

Answer 4

UCase applies to a single value only, so you can't use it for a multi-cell target. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    Application.EnableEvents = False

    For Each rng In Target.Cells

        If Not rng.HasFormula Then

            Select Case rng.Column

                Case 2, 3, 5

                    rng= UCase(rng)

            End Select

        End If

    Next rng

    Application.EnableEvents = True

End Sub