Worksheet_Change Event

[Guest]

Hell

I am trying to update a range that when a number is entered, a "#" is added to the beginning. I used the following code

Dim OSCksRange As Rang
Dim IntersectRange As Rang

Set OSCksRange = Range("OSCKs"
Set IntersectRange = Intersect(Target, OSCksRange

If Not IntersectRange Is Nothing The
Target.Value = "#" & Target.Valu
End I

If I enter anything into OSCKs range, the event keeps looping and appending '#' until the cell reaches its max number of characters. So I added the following code to the beginnin

If Left(Target, 1) = "#" The
Exit Su
End I

which solved that problem, but if I delete a character, it puts a "#" in the cell. So I tried addin

If Target.Value = Empty The
Exit Su
End I

which solved that problem, but if I delete more than one cell at a time I get a Type mismatch error

Is there a way to check and see what type of change has taken place, and if a number was entered run my original code

Any help would be appreciated

Thank
Sam

 

[Don Guillett]

try

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo fixit
Application.EnableEvents = False
If Not Intersect(Target, Range("f1:f5")) Is Nothing Then _
Target = "#" & Target
fixit: Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hello

I am trying to update a range that when a number is entered, a "#" is

added to the beginning. I used the following code.

Dim OSCksRange As Range
Dim IntersectRange As Range

Set OSCksRange = Range("OSCKs")
Set IntersectRange = Intersect(Target, OSCksRange)

If Not IntersectRange Is Nothing Then
Target.Value = "#" & Target.Value
End If

If I enter anything into OSCKs range, the event keeps looping and

appending '#' until the cell reaches its max number of characters. So I
added the following code to the beginning

If Left(Target, 1) = "#" Then
Exit Sub
End If

which solved that problem, but if I delete a character, it puts a "#" in the cell. So I tried adding

If Target.Value = Empty Then
Exit Sub
End If

which solved that problem, but if I delete more than one cell at a time I get a Type mismatch error.

Is there a way to check and see what type of change has taken place, and

if a number was entered run my original code?

 

[Anders S]

Sam,

Try

Application.EnableEvents =False
'your code here
Application.EnableEvents =True

Otherwise a new Worksheet_Change will be triggered everytime a "#" is entered.

HTH
Anders Silvén