Displaying different values without using formulas

[PO]

Excel 2003, sp2


Hi,

My worksheet has a group of cells (range A3:B10) where the user can enter
numeric values. The user can also enter a percentage value into range B1.
When the user enters the percentage value into B1 I want the numbers in
A3:B10 to display the original value multiplied by B1.

A3:B10 however doesn't contain any formulas (since the user can enter values
and "sabotage" the formulas). I want the original values to remain intact
and just display the new value

If I use macros to update the values I loose the original values.

Is this possible without using formulas (and without using macros), i.e.
using some kind of formatting code?

Regards
PO

 

[Pete_UK]

I'm not aware of any formatting code which will do this for you.

Pete

 

[Dave Peterson]

If you want to keep a value in those cells, you'll have to use a macro. In
fact, if you wanted to use a formula, you'd need to use some helper cells.

But how would you know what values are original? If I change a value (from 1 to
10, say) and the value in B1 is 110%, is my original value 1, 10, 1.10, or 11.0?

If you can decide, maybe you could put those original (whatever that means) on a
separate (hidden) sheet.

Without caring about the original values, you could use a worksheet event that
does the work:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng1 As Range
Dim myRng2 As Range

Set myRng1 = Me.Range("A3:B10")
Set myRng2 = Me.Range("B1")

With Target
If .Cells.Count > 1 Then
Exit Sub
End If

On Error GoTo ErrHandler:

If Not (Intersect(myRng1, .Cells) Is Nothing) Then
'in A3:B10
If IsNumeric(myRng2.Value) Then
If IsNumeric(.Value) Then
If IsEmpty(myRng2.Value) = False Then
Application.EnableEvents = False
.Value = .Value * myRng2.Value
End If
End If
End If
ElseIf Not (Intersect(myRng2, .Cells) Is Nothing) Then
'in B1
If IsNumeric(.Value) Then
If IsEmpty(.Value) = False Then
Application.EnableEvents = False
.Copy
myRng1.PasteSpecial Paste:=xlPasteValues, _
operation:=xlPasteSpecialOperationMultiply
Application.CutCopyMode = False
.Select
End If
End If
End If

End With

ErrHandler:
Application.EnableEvents = True

End Sub