worksheet_change event

Trishka88 · Mar 23, 2010

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 · Mar 25, 2010

Some additional thoughts.

Looking at your sheet, it seems to me that FUNCTIONALLY these ranges make more sense on the LEFT side...
[tt]
Cost PlusEntry MinusEntry
[/tt]
Cost FIRST because, that's where you have to start. Of course, these are the ONLY ranges that changes will take place DIRECTLY. The changes that occur in the OTHER ranges, are as a result of these three, either programatically or via a sheet formula.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

SkipVought · Mar 25, 2010

Also where you have your +/- Entry HEADINGS...
[tt]
="Plus "&CHOOSE(OptionChoice,"Entry","%")

="Minus "&CHOOSE(OptionChoice,"Entry","%")
[/tt]
to change the headings as the Option Button selection is made.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Trishka88 · Mar 28, 2010

Ok, thanks. I'll try them out and let you know.

Trishka88 · Mar 29, 2010

Would I be able to put another set of these code in the same sheet? Thanks

SkipVought · Mar 29, 2010

Would I be able to put another set of these code in the same sheet? Thanks

Lots of things possible.

What are the requirements?

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Trishka88 · Mar 29, 2010

e.g. I would one to monitor another cell change change event in the same worksheet via the change worksheet event

SkipVought · Mar 29, 2010

Sure, but it has to work with the existing code.

What are the requirements?

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Trishka88 · Mar 30, 2010

Let say, I would need another set in the same worksheet, but this time use a drop-down list.

By the way, I can't seem to protect the cells for the cost and original cost columns (H and I), I'm getting the "line 400 error".

Thanks

SkipVought · Mar 30, 2010

By the way, I can't seem to protect the cells for the cost and original cost columns

What PROCESS are you using?

Let say, I would need another set in the same worksheet, but this time use a drop-down list.

Use a Data > Valdation -- LIST to format a cell. Give the cell a Range Name. Let's assume the name is SelectedItem.

Code:

If Not Intersect(Target, [SelectedItem]) is Nothing then
  'stuff to do when [b]SelectedItem[/b] value changes.

end if

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Trishka88 · Mar 30, 2010

How do I protect the cells, I can't seem to protect the cells for the cost and original cost columns (H and I), I'm getting the "line 400 error". If I protected the spreadsheet, it wouldn't allow the plus/minus entry changes to appear

Trishka88 · Mar 30, 2010

By the way, I can't seem to protect the cells for the cost and original cost columns
What PROCESS are you using?

I tried using first via the protect sheet command in worksheet, then tried VBA:

Code:

    Range("H28:I42").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveSheet.Protect Password:="rg", DrawingObjects:=False, Contents:=True, Scenarios:= _
        False


    ActiveSheet.Unprotect Password:="rg"

I think I got the right code, but seemed misplaced. My objective is that the cost and adjusted cost columns are protected and cannot be changed. And can be unlocked by a password

SkipVought · Mar 30, 2010

If you protect the sheet, it is in order that USERS can only change cells that are unlocked.

So in your PROCEDURE, you must UNPROTECT the sheet before changing any protected cell and then PROTECT the sheet before exiting the procedure.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Trishka88 · Mar 30, 2010

How do I insert that in the procedure? I tried inserting the "protect" procedure after the 'end-if' then the unprotect after the "if-then". Note that users can change only our entry plus/minus columns, while when I unprotect I could change the cost columns which feeds to the adjusted columns. See below

Code:

Sub OptionChange()
    Application.EnableEvents = False
    Select Case [OptionChoice]
        Case 1  'Values
            With Range("PlusEntry")
                .Value = Range("Plus").Value
                .NumberFormat = "$#,##0.00"
            End With
            With Range("MinusEntry")
                .Value = Range("Minus").Value
                .NumberFormat = "$#,##0.00"
            End With
        Case 2  'Percent
            With Range("PlusEntry")
                .Value = Range("PlusPct").Value
                .NumberFormat = "0.0%"
            End With
            With Range("MinusEntry")
                .Value = Range("MinusPct").Value
                .NumberFormat = "0.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

    ActiveSheet.Unprotect Password:="rg"
                idx = Target.Row - Range("PlusEntry").Row + 1
                Range("Plus")(idx).Value = Target.Value
                Range("PlusPct")(idx).Value = Target.Value / Range("Cost")(idx)



    Range("H28:I42").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveSheet.Protect Password:="rg", DrawingObjects:=False, Contents:=True, Scenarios:=False

            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

    Range("H28:I42").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveSheet.Protect Password:="rg", DrawingObjects:=False, Contents:=True, Scenarios:=False

        Case 2  'Percent
            If Not Intersect(Target, Range("PlusEntry")) Is Nothing Then

    ActiveSheet.Unprotect Password:="rg"
                idx = Target.Row - Range("PlusEntry").Row + 1
                Range("Plus")(idx).Value = Target.Value * Range("Cost")(idx)
                Range("PlusPct")(idx).Value = Target.Value
            End If

    Range("H28:I42").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveSheet.Protect Password:="rg", DrawingObjects:=False, Contents:=True, Scenarios:=False
        
            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

    Range("H28:I42").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveSheet.Protect Password:="rg", DrawingObjects:=False, Contents:=True, Scenarios:=False

    End Select
    
    Range("Adjusted_Cost").Calculate
End Sub

SkipVought · Mar 30, 2010

Why are you changing the LOCKED property? You do that ONE TIME when you design the form.

So in your PROCEDURE, you must UNPROTECT the sheet before changing any protected cell and then PROTECT the sheet before exiting the procedure.

I guess that I really should have said,

So in your PROCEDURE, you must UNPROTECT the sheet before changing any LOCKED cell and then PROTECT the sheet before exiting the procedure.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Trishka88 · Mar 30, 2010

because, there will columns for the users and they can't change the entries in the cost and adjusted cost columns. I want that they could only change that part of the cell, however when I placed it in-between the codes I got the line 400 error

SkipVought · Mar 30, 2010

1) the COST, PLUS ENTRY & MINUS ENTRY ranges must have the Locked property assigned FALSE. All other cell must have the Locked property assigned TRUE. You do this ONE time and ONLY one time.

2) in any procedure where you need to CHANGE a LOCKED=TRUE cell, you must UNPROTECT the sheet, assign the value to the cell and PROTECT the sheet when you're done.

The users do not change the Adjusted Cost values. Those are CALCULATED values. Nothing changes IN THAT RANGE!

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Trishka88 · Mar 30, 2010

But if the users accidentally enter data in the adjusted cost column, that's the reason why I want to lock it. To be sure they only enter data in the cells where they could enter data only.

It seems that if there are computations in a cell or is part of a computation, if you had them locked the calculation gets an error message, is there a way to go around this?

SkipVought · Mar 30, 2010

But if the users accidentally enter data in the adjusted cost column, that's the reason why I want to lock it.

So what's keeping you for doing that?

It is what I have been suggesting. As I stated earlier...

the COST, PLUS ENTRY & MINUS ENTRY ranges must have the Locked property assigned FALSE. All other cell must have the Locked property assigned TRUE. You do this ONE time and ONLY one time.

What is the problem?

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Trishka88 · Mar 30, 2010

How do I do this via VBA, I tried (please see recent code inserted above where I inserted the

Code:

    Range("H28:I42").Select    Selection.Locked = True    Selection.FormulaHidden = False    ActiveSheet.Protect Password:="rg", DrawingObjects:=False, Contents:=True, Scenarios:=False

but I seem to be getting the error message. Or are you saying via the existing cell properties and then protect sheet route? Thanks!

SkipVought · Mar 30, 2010

Why do you INSIST on changing the Locked Property!!!???

ONE TIME when you design the form -- ONE TIME you assign the locked property!!!!!!!!

You NEVER change it again, unless you change the DESIGN OF THE FORM!!!

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim idx As Integer
    If Target.Count > 1 Then Exit Sub
'[b][red]========================================================================
'the Locked property is UNCHANGED!!!!!!!!!!!!!!
'========================================================================[/red]
'unprotect here
    ActiveSheet.Unprotect _
        Password:="rg"
'[/b]
    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
    
'[b]protect here
    ActiveSheet.Protect _
        Password:="rg", _
        DrawingObjects:=False, _
        Contents:=True, _
        Scenarios:=False
'[/b]
    
    Range("Adjusted_Cost").Calculate
End Sub

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

worksheet_change event

Technical User

Programmer

Programmer

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor