Worksheet_change error

G

Guest

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
 
G

Guest

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
 
G

Guest

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


Mike H said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
Combine Two Different Worksheet_Change codes 9
working with 2 or vba's on the same sheet 11
Create validation list with Worksheet_change 1
Worksheet_Change vs Worksheet_Calculate 2
Code in Excel 2000 template doesn't work in Excel 2010 1
preventing data entry if one cell's value smaller then zero 5
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0

Top