RTE 13 Type mismatch

[ipbr21054]

Evening,

I have a command button on my worksheet with the following code.
when ever i make an edit to how the sheet looks / works etc i use it to clear the cells so i can then save the file & this then is my new updated worksheet.
The code works fine BUT i get the annoying RTE as per title.
When i debug it i see the following shown in red below highlighted.

Private Sub ClearSheetValues_Click()
Dim answer As Integer
answer = MsgBox("THIS WILL CLEAR ALL CELL VALUES" & vbNewLine & "CLICK YES TO CONTINUE", vbYesNo + vbCritical, "ACCOUNTS CLEAR VALUES MESSAGE")
If answer = vbNo Then
  Exit Sub
Else
End If
Range("B1:D1").SpecialCells(xlCellTypeConstants).ClearContents
Range("A3:D29").SpecialCells(xlCellTypeConstants).ClearContents
Range("A34").SpecialCells(xlCellTypeConstants).ClearContents
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("D3:D29")) Is Nothing Then


If UCase(Target.Value) = "L" Then Target.Value = "4"
If UCase(Target.Value) = "W" Then Target.Value = "5"
If UCase(Target.Value) = "C" Then Target.Value = "7"


End If
    If Not (Application.Intersect(Target, Range("A2:D29")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If

End Sub

When i use the worksheet i type L in the cell in column D then once i leave the cell the L is replaced by the value 4
This is just speed related save me typing it out all the time.

This isnt any issue but its annoying to keep the RTE pop up.
Can we somehow stop it.

Many thansk

 

[RoryA]

You should disable events while clearing the cells, by using Application.Enableevents = False before the clearcontents lines and then Application.Enableevents = True afterwards. Also, don't use Specialcells applied to a single cell - if you do, it will actually be applied to the entire sheet.

 

[ipbr21054]

Thanks,

So would then be correct ?

Private Sub ClearSheetValues_Click()
Dim answer As Integer
answer = MsgBox("THIS WILL CLEAR ALL CELL VALUES" & vbNewLine & "CLICK YES TO CONTINUE", vbYesNo + vbCritical, "ACCOUNTS CLEAR VALUES MESSAGE")
If answer = vbNo Then
  Exit Sub
Else
End If
Application.EnableEvents = False
Range("B1:D1").ClearContents
Range("A3:D29").ClearContents
Range("A34").ClearContents
Application.EnableEvents = True
End Sub

 

[RoryA]

It depends. Do you have any formula cells in those ranges that you need to preserve?

 

[ipbr21054]

Only just a normal =Sum for this column D

 

[RoryA]

Then you should keep the SpecialCells bit for the first two ClearContents lines. It just doesn't make sense for the last one.

 

[ipbr21054]

OK
Ive put it back to how it was with ref special etc but removed it for A34