I want to create a template that I can use to import a series of numbers into
a column and then have that number changed to a negative number. I tried
placing a formula in column C that would multiply the number in column B by
-1 and display the result in column C.

However when I used the template to create a document, the formula was not
carried to the new document. Any suggestions?

How about event code to change the numbers as they are entered?

No formulas involved.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Target.Column < 1 And _
Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value * -1
'.NumberFormat = "0;<0"
Application.EnableEvents = True
End With
End Sub

Open a new workbook. Customize as you wish then place this event code into the
sheet.

Right-click on the the sheet tab and "View Code".

Copy/paste into that sheet module.

Now................FileSave AsFile TypeExcel Template(*.xlt)

Give it a name and store it where you like.

I have no idea why your original template had no formulas unless you did not
save correctly.


Gord Dibben MS Excel MVP



On Mon, 14 May 2007 13:58:03 -0700, Dennis
wrote:

I want to create a template that I can use to import a series of numbers into
a column and then have that number changed to a negative number. I tried
placing a formula in column C that would multiply the number in column B by
-1 and display the result in column C.

However when I used the template to create a document, the formula was not
carried to the new document. Any suggestions?

Gord,

It works great but how do I limit the action to a single column?

"Gord Dibben" wrote:

How about event code to change the numbers as they are entered?

No formulas involved.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Target.Column < 1 And _
Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value * -1
'.NumberFormat = "0;<0"
Application.EnableEvents = True
End With
End Sub

Open a new workbook. Customize as you wish then place this event code into the
sheet.

Right-click on the the sheet tab and "View Code".

Copy/paste into that sheet module.

Now................FileSave AsFile TypeExcel Template(*.xlt)

Give it a name and store it where you like.

I have no idea why your original template had no formulas unless you did not
save correctly.


Gord Dibben MS Excel MVP



On Mon, 14 May 2007 13:58:03 -0700, Dennis
wrote:

I want to create a template that I can use to import a series of numbers into
a column and then have that number changed to a negative number. I tried
placing a formula in column C that would multiply the number in column B by
-1 and display the result in column C.

However when I used the template to create a document, the formula was not
carried to the new document. Any suggestions?

One way:


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub

Application.EnableEvents = False
With Target
.Value = Abs(.Value) * -1
'.NumberFormat = "0;<0"
End With
Application.EnableEvents = True
End Sub

I rearranged some stuff--for just arbitrary reasons.


Dennis wrote:

Gord,

It works great but how do I limit the action to a single column?

"Gord Dibben" wrote:

How about event code to change the numbers as they are entered?

No formulas involved.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Target.Column < 1 And _
Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value * -1
'.NumberFormat = "0;<0"
Application.EnableEvents = True
End With
End Sub

Open a new workbook. Customize as you wish then place this event code into the
sheet.

Right-click on the the sheet tab and "View Code".

Copy/paste into that sheet module.

Now................FileSave AsFile TypeExcel Template(*.xlt)

Give it a name and store it where you like.

I have no idea why your original template had no formulas unless you did not
save correctly.


Gord Dibben MS Excel MVP



On Mon, 14 May 2007 13:58:03 -0700, Dennis
wrote:

I want to create a template that I can use to import a series of numbers into
a column and then have that number changed to a negative number. I tried
placing a formula in column C that would multiply the number in column B by
-1 and display the result in column C.

However when I used the template to create a document, the formula was not
carried to the new document. Any suggestions?





--

Dave Peterson

Sorry about that Dennis.

Try this construct............change the ("A:A") to your choice.

OR ("A:A, C:C, E:E") for cols A, C and E

OR ("A1:B23") for a range

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit:
Application.EnableEvents = False
For Each rng In vRngInput
If IsNumeric(rng.Value) Then

With rng
.Value = .Value * -1
'.NumberFormat = "0;<0"

End With
End If
Next
endit:
Application.EnableEvents = True
End Sub


Gord

On Mon, 14 May 2007 17:27:00 -0700, Dennis
wrote:

Gord,

It works great but how do I limit the action to a single column?

"Gord Dibben" wrote:

How about event code to change the numbers as they are entered?

No formulas involved.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Target.Column < 1 And _
Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value * -1
'.NumberFormat = "0;<0"
Application.EnableEvents = True
End With
End Sub

Open a new workbook. Customize as you wish then place this event code into the
sheet.

Right-click on the the sheet tab and "View Code".

Copy/paste into that sheet module.

Now................FileSave AsFile TypeExcel Template(*.xlt)

Give it a name and store it where you like.

I have no idea why your original template had no formulas unless you did not
save correctly.


Gord Dibben MS Excel MVP



On Mon, 14 May 2007 13:58:03 -0700, Dennis
wrote:

I want to create a template that I can use to import a series of numbers into
a column and then have that number changed to a negative number. I tried
placing a formula in column C that would multiply the number in column B by
-1 and display the result in column C.

However when I used the template to create a document, the formula was not
carried to the new document. Any suggestions?