Clever Geek Handbook

Excel VBA event for calculation based on user input

To preface this situation, I'm new to VBA programming, so any help would be greatly appreciated.

I have two columns; where the user can enter a dollar value ("AL") and another where the user can enter a percentage value ("AK"). The object is to allow the user to enter either a value (% or $) or calculate another value. For example, if the user enters 10% in "AL", the corresponding $ value will be generated in "AK" and vice versa.

Below is the code I have come up with so far, but it doesn't work. Any thoughts / suggestions would be greatly appreciated! Thank!

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim cell As Range
   Set cell = Range("AK9:AL50")
   'Application.EnableEvents = False Application.EnableEvents = True'

   If Not Application.Intersect(cell, Range(Target.Address)) Is Nothing Then
    If Target.Column = 37 Then ' Value in first column changed
        Range("AL" & Target.Row).Value = Range("AK" & Target.Row).Value / Range("V" & Target.Row).Value
        Exit Sub
    ElseIf Target.Column = 38 Then ' value in second column changed
        Range("AK" & Target.Row).Value = Range("AL" & Target.Row).Value * Range("V" & Target.Row).Value
        Exit Sub
    'Application.EnableEvents = False Application.EnableEvents = True'
    End If
  End If
End Sub
+3


source to share


2 answers


You might be better off using your options Worksheet_Change

for example Target

.

1. Instead of:

Range("AL" & Target.Row).Value

you can use:

Target.Offset(, 1).Value

2. Instead of:



Range("AK" & Target.Row).Value

you can use:

Target.Value

3.Also Range(Target.Address)

actuallyTarget

code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Set cell = Range("AK9:AL50")

Application.EnableEvents = False

If Not Application.Intersect(cell, Target) Is Nothing Then
    If Target.Column = 37 Then ' Value in first column changed
        Target.Offset(, 1).Value = Target.Value / Range("V" & Target.Row).Value
    ElseIf Target.Column = 38 Then ' value in second column changed
        Target.Offset(, 2).Value = Target.Value * Range("V" & Target.Row).Value
    End If
End If
Application.EnableEvents = True '<-- RESTORE SETTING OUTSIDE THE IF

End Sub
+1


source


You need to remove Exit Sub

s

And Application.EnableEvents = True

must be outside the if.

The first time you started it with a string Application.EnableEvents = False

it turned off events, and after you exited the sub, before turning them back on, it stayed off and south was no longer called.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim cell As Range
   Set cell = Range("AK9:AL50")
   Application.EnableEvents = False

   If Not Application.Intersect(cell, Range(Target.Address)) Is Nothing Then
    If Target.Column = 37 Then ' Value in first column changed
        Range("AL" & Target.Row).Value = Range("AK" & Target.Row).Value / Range("V" & Target.Row).Value

    ElseIf Target.Column = 38 Then ' value in second column changed
        Range("AK" & Target.Row).Value = Range("AL" & Target.Row).Value * Range("V" & Target.Row).Value

    End If
    Application.EnableEvents = True
  End If
End Sub


My guess right now, your events are disabled.

Run this code after setting the correct code in the sheet:

Sub foooo()
Application.EnableEvents = True
End Sub

This will re-enable events. It only needs to be done once.

+2


source






More articles:


All Articles