Applying formula to active cell

[harky]

Hi

I have a spreadsheet where I want user values to be multiplied by 9 automatically when they are typed in. Eg, when a user types 5 into cel E6 i want it to automatically change to 45. How can you have a formula run in the background for a cell and still be able to type in values without overwriting the formula?

Any advice appreciated

 

[Loomah]

You can't have it in the same cell.

The effect you want can be achieved however using VBA

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Target = Target * 5
Application.EnableEvents = True
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[harky]

Cheers Loomah

That worked a treat

 

[firefytr]

With a couple of extra checks ...

Option Explicit

Const Mult_Range As String = "A1:B10"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range(Mult_Range)) Is Nothing Then Exit Sub
    If Target.HasFormula Then Exit Sub
    If Not IsNumeric(Target.Value) Then Exit Sub
    Application.EnableEvents = False
    Target.Value = Target.Value * 9
    Application.EnableEvents = True
End Sub

-----------
Regards,
Zack Barresse