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.

 

[Trishka88]

I placed it in the code, but get line 400 error after I change the option buttons (please see file).

I'm looking if I could integrate the code below in the existing code in the attached file

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

'   Local Variables
    Dim strName             As String
    Dim strRange            As String

'   Step 1 : Check Value of entered name
    strName = Me.Range("B1")
    If strName = "" Then
        Me.Unprotect
        Me.Cells.Locked = True
        Me.Cells.FormulaHidden = True
        Me.Range("B1").Locked = False
        Me.Protect
    End If

'   Step 2 : Unlock desired cells based on value
    If Me.Range("B1") <> "" Then
        Select Case Me.Range("B1")
            Case "Hello": strRange = "B3:G3"
            Case "Goodbye": strRange = "B5:G5"
        End Select
        If strRange <> "" Then
            Me.Unprotect
            Me.Cells.Locked = True
            Me.Cells.FormulaHidden = True
            Me.Range(strRange).Locked = False
            Me.Range("B1").Locked = False
            Me.Protect
        End If
    End If
End Sub

so to unprotect it, a word would just be entered on a cell to unlock it and then delete it lock it again.

Thanks

 

[SkipVought]

1) UNLOCK cell OptionChange

2) Your code with additions

Sub OptionChange()
    Application.EnableEvents = False
    '[b]unprotect here
    ActiveSheet.Unprotect _
        Password:="rg"
    '[/b]
        
    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
    '[b]protect here
    ActiveSheet.Protect _
        Password:="rg", _
        DrawingObjects:=False, _
        Contents:=True, _
        Scenarios:=False
     '[/b]
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim idx As Integer
    If Target.Count > 1 Then Exit Sub
    '[b]turn off event processing
    Application.EnableEvents = False
    
    '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
'turn on event processing
    Application.EnableEvents = True
'[/b]
    
    Range("Adjusted_Cost").Calculate
End Sub

Skip,

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

 

[Trishka88]

It works, thank you. I need to put control in my check box, currently this displays "hello", can it be possible to display a message box with a text box, then if the right text is entered, it will proceed to the next line:

Private Sub CheckBox1_Click()
 'If checkbox is selected, it runs TRUE.
 'If checkbox is not selected, it runs FALSE
 'Swap out the msgbox for your code

If CheckBox1 = True Then
    
    Columns("I").Hidden = True
    Range("b28").Select
    CheckBox1.Caption = "Uncheck to enter orig. cost"
Else
'instead of getting "hello" message
    MsgBox ("hello")

'it would ask for a text input
'if its correct it would proceed to hide column

    Columns("I").Hidden = False
    CheckBox1.Caption = "Check to hide orig. cost"
    Range("b28").Select
End If

End Sub

 

[SkipVought]

box, then if the right text is entered, it will proceed to the next

Well what text would be correct?

What happens if NOT correct text?

Skip,

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

 

[Trishka88]

correct text is "change cost" entered in the message box for example. If it's not correct it will run the checkbox = true and if correct will run checkbox = true if not, it will run from columns("I").Hidden=False

 

[SkipVought]

I really could not figure out what you meant, but here's how an InputBox works...

dim answer

answer = inputbox("Please enter the correct text")

if answer = "change cost" then
  'stuff to do if TRUE
else
  'stuff to do if FALSE
end if

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]