Worksheet_change error
 

Hi All
I have a great code that replaces characters with symbols - and it works
brilliantly, until I select multiple cells on the worksheet and hit the
delete key.
I then get a "Run-time error '13': Type mismatch" error message.
Can someone tell me what I need to add to this code to stop the error if a
user hits the delete key?

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Target
Dim s As String
s = r.Value
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, "", ChrW(769))
s = Replace(s, "~", ChrW(776))
Application.EnableEvents = False
r.Value = s
Application.EnableEvents = True
End Sub
--
Thank for your help
BeSmart

Worksheet_change error
 

Try:-

Private Sub Worksheet_Change(ByVal target As Range)
Application.EnableEvents = False
Dim s As String
Set myRange = target
For Each r In myRange
s = r.Value
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, "", ChrW(769))
s = Replace(s, "~", ChrW(776))
r.Value = s
Next
Application.EnableEvents = True
End Sub

Mike


"BeSmart" wrote:

Hi All
I have a great code that replaces characters with symbols - and it works
brilliantly, until I select multiple cells on the worksheet and hit the
delete key.
I then get a "Run-time error '13': Type mismatch" error message.
Can someone tell me what I need to add to this code to stop the error if a
user hits the delete key?

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Target
Dim s As String
s = r.Value
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, "", ChrW(769))
s = Replace(s, "~", ChrW(776))
Application.EnableEvents = False
r.Value = s
Application.EnableEvents = True
End Sub
--
Thank for your help
BeSmart

Worksheet_change error
 

Fantastic - thanks heaps Mike!!!!
--
BeSmart


"Mike H" wrote:

Try:-

Private Sub Worksheet_Change(ByVal target As Range)
Application.EnableEvents = False
Dim s As String
Set myRange = target
For Each r In myRange
s = r.Value
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, "", ChrW(769))
s = Replace(s, "~", ChrW(776))
r.Value = s
Next
Application.EnableEvents = True
End Sub

Mike


"BeSmart" wrote:

Hi All
I have a great code that replaces characters with symbols - and it works
brilliantly, until I select multiple cells on the worksheet and hit the
delete key.
I then get a "Run-time error '13': Type mismatch" error message.
Can someone tell me what I need to add to this code to stop the error if a
user hits the delete key?

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Target
Dim s As String
s = r.Value
s = Replace(s, "[", ChrW(780))
s = Replace(s, "]", ChrW(768))
s = Replace(s, "<", ChrW(772))
s = Replace(s, "", ChrW(769))
s = Replace(s, "~", ChrW(776))
Application.EnableEvents = False
r.Value = s
Application.EnableEvents = True
End Sub
--
Thank for your help
BeSmart