you turned .enableevents off

Yes, that's what I did. I had it in the wrong place.

Thanks, Dave!

Biff

"Dave Peterson" wrote in message
...
Pasting should fire the _change event. I'm guessing that in your testing,
you
turned .enableevents off (maybe when one test version broke). And it was
never
turned back on.

But this kind of thing worked ok for me--I wasn't sure about the
formatting,
though.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myStr As String
Dim myCell As Range
Dim myRng As Range

Set myRng = Intersect(Me.Range("C:C"), Target)

If myRng Is Nothing Then Exit Sub

On Error Resume Next
For Each myCell In myRng.Cells 'just column C
With myCell
myStr = Replace(.Value, "-", "")
If IsNumeric(myStr) Then
.NumberFormat = String(12, "0")
Application.EnableEvents = False
.Value = myStr
Application.EnableEvents = True
End If
End With
Next myCell
On Error GoTo 0

End Sub


"T. Valko" wrote:

I tried writing an event macro to do this.

A couple of things that caused problems:

Pasting into a target cell doesn't fire the macro.

Excel insists on converting 123451234561 to scientific notation:
1.23E+11.
Setting the target format to:

Target.NumberFormat = "@"

does set the format to TEXT, however, it still displays as text 1.23E+11
not
text 123451234561. Widening the column didn't make a difference either.

I didn't save the code so I can't post it.

Biff

"Martin Fishlock" wrote in message
...
Hi Faraz:

Try this little gem. It replaces the active sheet only:

Sub hyphensconvertcolConly()
ActiveSheet.Columns("C:C").Replace _
What:="-", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Merss around with
ActiveSheet.Columns("C:C").

to change different sheets or columns:

as in activeworkbook.worksheets("Sheet1").Columns("D:G") .
.....
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"FARAZ QURESHI" wrote:

Thanx Ed

But I have to consolidate several statements received in parts. Is
there
a
way 2 "automatically" replace the hyphens? A VBA Macro is welcomed.

"Ed Ferrero" wrote:

Hi FARAZ QURESHI,

Select the data you wish to convert. Use Edit - Replace, and
replace
"-"
with nothing.

Ed Ferrero
www.edferrero.com

I am looking forward for a macro or any other way to convert my
data:

12345-123456-1

when pasted into a column lets suppose C:C, to:

123451234561

i.e. hyphens removed automatically.
Can anyone please help?
Thanking in advance




--

Dave Peterson