RTE 13 Type mismatch

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB code):
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


Rich (BB code):
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,701
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
Thanks,

So would then be correct ?

Rich (BB code):
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,701
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It depends. Do you have any formula cells in those ranges that you need to preserve?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Only just a normal =Sum for this column D
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,701
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Then you should keep the SpecialCells bit for the first two ClearContents lines. It just doesn't make sense for the last one.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
OK
Ive put it back to how it was with ref special etc but removed it for A34
 

Watch MrExcel Video

Forum statistics

Threads
1,129,689
Messages
5,637,837
Members
416,985
Latest member
mrindira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top