I have an Excel spreadsheet that works as a service report. Several of the
cells represent costs(i.e. Hotel, Rental Car, etc.). I have installed a
checkbox that can be checked if a customer meets certain criteria. If
checked, it adds and then discounts a range of cells by 10%. Easy enough. I
have had some requests by customers to reflect the discount on a per cell
basis, so if service rep enters:
Hotel $100.00
when the checkbox is checked the same cell changes to reflect the discount:
Hotel $ 90.00
The IF statement is simple enough, but the formula gets overwritten the
minute the user enters the amount of the expense, so.....
Can I modify the data entered without creating a circular reference?, or..
Can I attach a formula to a cell that cannot be overwritten by user input?
( basically, a hidden formula)

Thanks to all,
Tim

Do you want it to hold 100 and show 90 or do you want it to hold 90. If the
answer is to hold 90 here is some code that will apply a discount to cells C2
through C10 based on if the check box is checked or not.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cbxMyCheckBox As Shape
Dim rngToAdjust As Range

On Error GoTo ErrorHandler
Set rngToAdjust = Range("C2:C10")
If Not Intersect(Target, rngToAdjust) Is Nothing Then
Set cbxMyCheckBox = Sheet1.Shapes("Check Box 1")
If cbxMyCheckBox.ControlFormat.Value = 1 And IsNumeric(Target.Value)
Then
Application.EnableEvents = False
Target.Value = Target.Value - (Target.Value * 0.1)
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

Jim Thomlinson


"Tim Richards" wrote:

I have an Excel spreadsheet that works as a service report. Several of the
cells represent costs(i.e. Hotel, Rental Car, etc.). I have installed a
checkbox that can be checked if a customer meets certain criteria. If
checked, it adds and then discounts a range of cells by 10%. Easy enough. I
have had some requests by customers to reflect the discount on a per cell
basis, so if service rep enters:
Hotel $100.00
when the checkbox is checked the same cell changes to reflect the discount:
Hotel $ 90.00
The IF statement is simple enough, but the formula gets overwritten the
minute the user enters the amount of the expense, so.....
Can I modify the data entered without creating a circular reference?, or..
Can I attach a formula to a cell that cannot be overwritten by user input?
( basically, a hidden formula)

Thanks to all,
Tim