worksheet_change event

[Trishka88]

Hi,

Hope you could help me with this. This is an "amount estimate" table. The computation centers on the Amt column. There will be a set amount in this column, e.g. 400. Then two columns on the left, to adjust values, one to increase (+) and the other to decrease (-) value in the Amt column. e.g. a 100 in increase(+)column and a 200 in the decrease (-) column, makes the Amt column to 300.

On the right of the Amt column is another set of two columns, this time its percent increase (+%) and the other column is percent decrease (-%). These two columns will represent the % expression of the increase (+) column and the decrease (+) column, i.e. 25% for the percent increase and 50% for the percent decrease.

Now, the amount initially entered should adjust based on the entries in either the percent or values columns. Example, this will be done by inserting a value in either the increase or decrease column. When that value is added it needs the value to automatically be added to Amt column and then show the % increase or % decrease. It should also work the opther way. If a value is entered at the increase or decrease using the % columns, they have to just put a value in the +% or -% column and it will automatically show the value that it has increased or decreased the Amt column.

 

[SkipVought]

Hi,

Your VB Project is password protected!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Trishka88]

password is rgusila1201

 

[Trishka88]

This one is not password protected

 

[SkipVought]

OK.

Why are you using VBA? Looks to me that the sheet could perform with native Excel features.

Please explain.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Trishka88]

Thanks for the reply. A value will be entered in the "cost" column, then if values in the percent (+,-) column change it will reflect in the "cost" column as well and the plus minus value column will adjust according the percent (+,-) column. And can be the other way around, that is, entering in the plus / minus values column will change the value on the "cost" column and the percent (+,-) column.

What seems to need VBA, is that these changes in the 4 columns just reflects in one column (plus its original entry)

 

[SkipVought]

I do not understand at all.

You're using VBA to put FORMULAS in these cells.

Once the formulas are in the cells, ther is NOTHING else to do via VBA. There is no added value to EVER run the code again!!!

???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Trishka88]

This is a cost form. I fill in the cost column, then I give this out to other users to who will adjust values by either adding or subtracting to the cost that I have already entered. This will be done by inserting a value in either the plus or minus columns. When that value is added I need that value to automatically be added to the cost column and then show the percent increase or percent decrease. I also want to work in reverse, if the user add or subtract using the percent columns, all they have to do is put a value in the percent (+) or (-) column and it will show the value in the cost column that they have increased or decreased my cost, which will also adjust in the values column (plus / minus columns)

 

[Trishka88]

Hi, my initial approach was, change in any cell in percent columns (F5:G7) = write formula in cost (E column) and values columns (C57). Then change in values columns, write formula in cost column and percent columns. I'm not sure if this is the right way to approach it.

Originally, there is no column "K3:K7". All cost should be entered only in E5:E7, please see uploaded second file which references these cells I mentioned. Thanks

 

[SkipVought]

So functionally, the user will EITHER enter +/- VALUES or +/- PERCENTS, but NEVER both?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Trishka88]

They user will enter either plus/minus values or plus/minus percent. And they are dependent of each others values. Likewise, with the cost column.

 

[Trishka88]

The user can tweak both the plus/minus and percent plus/minus column altogether to get to the cost.

 

[SkipVought]

So you have a 2 column range that will be disignated by a control as either Plus/Minus or Plus Pct/Minus Pct, used to enter these values. This is new.

If the headings are Plus/Minus, the values are assigned to the current Plus/Minus columns and the current Plus Pct/Minus Pct columns as calculated values.

If the headings are Plus Pct/Minus Pct, the values are assigned to the current Plus Pct/Minus Pct columns and the current Plus/Minus columns as calculated values.

This would be the cleanest. The Worksheet_Change event would only produce a result, when a value in this new range changes.

Would that work for you?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Trishka88]

Yes, that should work: (+,-) columns when values are entered into it turns percent (+,-) columns and "cost" column as calculated values. And values entered in percent (+,-) columns turns the (+,-) columns and "cost" columns to calculated columns.

Just don't know if I had started it right or placed the codes as right.

 

[SkipVought]

Here's what I suggest...
[tt]
1. ALL data ranges as Named Ranges, base on heading values (use Insert > Name > Create -- Create names in TOP row)

Mine are
PlusEntry [highlight]NEW[/highlight]
MinusEntry [highlight]NEW[/highlight]
Plus
Minus
Adjusted Cost
PlusPct
MinusPct

Cost

2. a set of 2 Forms Option Buttons, linked to a cell Named OptionChoice and both buttons assigned to OptionChange.
[/tt]
Code:

Sub OptionChange()
    Application.EnableEvents = False
    Select Case [OptionChoice]
        Case 1  'Values
            With Range("PlusEntry")
                .Value = Range("Plus").Value
                .NumberFormat = "General"
            End With
            With Range("MinusEntry")
                .Value = Range("Minus").Value
                .NumberFormat = "General"
            End With
        Case 2  'Percent
            With Range("PlusEntry")
                .Value = Range("PlusPct").Value
                .NumberFormat = "0%"
            End With
            With Range("MinusEntry")
                .Value = Range("MinusPct").Value
                .NumberFormat = "0%"
            End With
    End Select
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim idx As Integer
    If Target.Count > 1 Then Exit Sub
    
    Select Case [OptionChoice]
        Case 1  'Values
            If Not Intersect(Target, Range("PlusEntry")) Is Nothing Then
                idx = Target.Row - Range("PlusEntry").Row + 1
                Range("Plus")(idx).Value = Target.Value
                Range("PlusPct")(idx).Value = Target.Value / Range("Cost")(idx)
            End If
        
            If Not Intersect(Target, Range("MinusEntry")) Is Nothing Then
                idx = Target.Row - Range("MinusEntry").Row + 1
                Range("Minus")(idx).Value = Target.Value
                Range("MinusPct")(idx).Value = Target.Value / Range("Cost")(idx)
            End If
        Case 2  'Percent
            If Not Intersect(Target, Range("PlusEntry")) Is Nothing Then
                idx = Target.Row - Range("PlusEntry").Row + 1
                Range("Plus")(idx).Value = Target.Value * Range("Cost")(idx)
                Range("PlusPct")(idx).Value = Target.Value
            End If
        
            If Not Intersect(Target, Range("MinusEntry")) Is Nothing Then
                idx = Target.Row - Range("MinusEntry").Row + 1
                Range("Minus")(idx).Value = Target.Value * Range("Cost")(idx)
                Range("MinusPct")(idx).Value = Target.Value
            End If
    End Select
    
    Range("Adjusted_Cost").Calculate
End Sub

I would also UNLOCK the Entry ranges and the Cost range, and then PROTECT the sheet, allowing only unlocked cells to be selected.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Trishka88]

I think, I'm not sure I got the cell addresses of the "named" range correctly. Please see attached.

 

[SkipVought]

I stated TWO NEW RANGES!
[tt]
PlusEntry MinusEntry Plus Minus Adjusted Cost PlusPct MinusPct COST
[/tt]
The NEW RANGES are in 2 NEW COLUMNS just for data entry.

ALL the ranges MUST be the same size. This include the EMPTY ROW 4 in your sheet

I would INSERT a row below your heading, with those headings, and use Insert > Name > Create... with those headings. Then Hide the inserted row.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Trishka88]

Hello Skip,

When I click on the option, it doesn't seem to recalculate the "Adjusted_Cost" column (see blue cell, G6:G8).

Question: Are our user entries going in the red cells - C68? While my entry will only be in the orange cells - H6:H8? While the yellow - E6:F8, blue - G6:G8 and green - I6:J8 will auto-calculate?

I placed the expected recalculted value in row 16. And equivalent value of the percent entry, I16:J16 (20%, 0%) and its equivalent at E16:F16, (40 and 0). Based on cost of 200, adjusted is 240.

Did I label the ranges correctly or are the cells I used as entry the correct entry cells?


Thanks!
Trishka88

 

[Trishka88]

P.S. I've attached the Sample_Updated2, thanks you (above)

 

[SkipVought]

When I click on the option, it doesn't seem to recalculate the "Adjusted_Cost" column (see blue cell, G6:G8).

Well! You removed your formula!!! What do you expect?

Question: Are our user entries going in the red cells - C68? While my entry will only be in the orange cells - H6:H8? While the yellow - E6:F8, blue - G6:G8 and green - I6:J8 will auto-calculate?

Yes. Yes. Yes.

So here's what you need to fix.
[tt]
The ONLY calculated cells are in the Adjusted Cost column
=Cost+Plus-Minus in all cells. Delete the formulas in the other columns.
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}