Worksheet Change event

  • I'd like to use event code to set two variables in order to pass them to a sub (module) for further processing. I can use event code for one variable - no problem. Is it possible to perform the same trick for two variables ?

    Something like this code:

    Thank you.

  • Here is where LocVar gets assigned:

    If Not Intersect(Target, Range("F9")) Is Nothing Then
        Range("A" & Sheets("VBA Data").Range("O35")).Select '<---- takes user where he needs to be
        LocVar = "AFR"
    End If

    but when you get to the Second Step, since your macro has skipped First Step because you aren't in cell F9, LocVar will not be assigned, that's why when you are in cells N103-N130 your variable will result empty.

    For j = 0 To 27                               '28 day planning horizon
        If Not Intersect(Target, Range("N" & 103 + j)) Is Nothing Then
            Dt_Req = Target.Value
            'Call Loc_Summary(LocVar, Dt_Req)
            MsgBox LocVar & " , " & Dt_Req        '<- - - - - - -now, the box shows ", 02/05/2022" What made LocVar disappear ?? Retriggering the event ?
        End If                                    '  ^-------------------------^
  • I've been thinking about your explanation.

    In an empty workbook , I've put some strings in range E:5 to E10. On sheet level, I run this code:

    And on module level:

    Sub AcceptVar(ByVal Loc As String)
    Range("A1").Value = Loc
    End Sub

    Click on a cell in E5 to E10, >>> A1 gets the value of the string clicked. Then, if you click anywhere outside that range, the string in A1 disappears.

    It is exactly this behavior I don't understand. Why the 'reset' ? You would expect 'overwrite' as per VBA code.

    Interestingly, if I place the call for the sub inside the If block, cell A1 remains as it is, that is, when clicking outside the E range. Click inside the E range and hurray, the desired change takes place. So yes, I am still scratching my head ....

    Again, muchas gracias !

  • As per your example, since your Dim Loc As String is inside the event Sub Worksheet_SelectionChange it will inizialize (to empty) everytime the event triggers. Move the Dim just before the Sub and your variable will retain the last input.

    If you put the Call inside the If/Then, and you are clicking outside the range, the Call will never execute; therefore A1 will not change.

    Edited once, last by rollis13 ().

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!