I have a format like the following for distribution to branches:

Code Name Amount
101-2 ABC 100,000

To alert branch manger upon incorrect data insertion I have customized the
format of Code column as 000-0 and have instructed the managers to enter data
without dashes like 1012 while the conditional formatting set to highlight
cells with code <1000 and 9999 so as to confirm only a four digit one
inserted.

The branch managers usually paste the data despite of all the instructions.
I want to have a macro applied which would upon pasting the data either by
"Ctrl+V" or "Enter" have the data automatically paste special values and
remove the dashes/hyphens.

Thanx in Advance!

Use a worksheet_change function. Right click tab on bottom of worksheet
(normally sheet1) and select view code. then paste following code.

Sub worksheet_change(ByVal target As Range)

For Each cell In target

cell.Value = Replace(cell, "-", "")

Next cell

End Sub


"FARAZ QURESHI" wrote:

I have a format like the following for distribution to branches:

Code Name Amount
101-2 ABC 100,000

To alert branch manger upon incorrect data insertion I have customized the
format of Code column as 000-0 and have instructed the managers to enter data
without dashes like 1012 while the conditional formatting set to highlight
cells with code <1000 and 9999 so as to confirm only a four digit one
inserted.

The branch managers usually paste the data despite of all the instructions.
I want to have a macro applied which would upon pasting the data either by
"Ctrl+V" or "Enter" have the data automatically paste special values and
remove the dashes/hyphens.

Thanx in Advance!

Wowo That sure was great Joel!

However, how can I apply the code only for column A and have the paste be
made only in special mode allowing values.

"Joel" wrote:

Use a worksheet_change function. Right click tab on bottom of worksheet
(normally sheet1) and select view code. then paste following code.

Sub worksheet_change(ByVal target As Range)

For Each cell In target

cell.Value = Replace(cell, "-", "")

Next cell

End Sub


"FARAZ QURESHI" wrote:

I have a format like the following for distribution to branches:

Code Name Amount
101-2 ABC 100,000

To alert branch manger upon incorrect data insertion I have customized the
format of Code column as 000-0 and have instructed the managers to enter data
without dashes like 1012 while the conditional formatting set to highlight
cells with code <1000 and 9999 so as to confirm only a four digit one
inserted.

The branch managers usually paste the data despite of all the instructions.
I want to have a macro applied which would upon pasting the data either by
"Ctrl+V" or "Enter" have the data automatically paste special values and
remove the dashes/hyphens.

Thanx in Advance!

How do you know when you are in a special mode? You can use a cell like A1
to be the mode indicator and then add an if statement in code below that
looks at cell A1. I modifiied the code below so it will look at onlhy column
A.


Sub worksheet_change(ByVal target As Range)

For Each cell In target

if cell.column = 1 then
cell.Value = Replace(cell, "-", "")
end if
Next cell

End Sub


"FARAZ QURESHI" wrote:

Wowo That sure was great Joel!

However, how can I apply the code only for column A and have the paste be
made only in special mode allowing values.

"Joel" wrote:

Use a worksheet_change function. Right click tab on bottom of worksheet
(normally sheet1) and select view code. then paste following code.

Sub worksheet_change(ByVal target As Range)

For Each cell In target

cell.Value = Replace(cell, "-", "")

Next cell

End Sub


"FARAZ QURESHI" wrote:

I have a format like the following for distribution to branches:

Code Name Amount
101-2 ABC 100,000

To alert branch manger upon incorrect data insertion I have customized the
format of Code column as 000-0 and have instructed the managers to enter data
without dashes like 1012 while the conditional formatting set to highlight
cells with code <1000 and 9999 so as to confirm only a four digit one
inserted.

The branch managers usually paste the data despite of all the instructions.
I want to have a macro applied which would upon pasting the data either by
"Ctrl+V" or "Enter" have the data automatically paste special values and
remove the dashes/hyphens.

Thanx in Advance!