Worksheet Change event

51degreesNorh · May 2nd 2022

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:

Code

    Dim Dt_Req As Date
    Dim LocVar As String
    
    Application.EnableEvents = False

'First step: select location

    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
    MsgBox LocVar & "," & Dt_Req '<- - - Box shows LocVar and 00:00:00 - makes sense

'Second step: user sets requested date by picking it
   
    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                                                        '  ^-------------------------^

    Next

    Application.EnableEvents = False

Display More

Thank you.

rollis13 · May 2nd 2022

Here is where LocVar gets assigned:

Code

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.

Code

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                                    '  ^-------------------------^
Next

51degreesNorh · May 4th 2022

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:

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 Dim MyRange As Range
 Dim Loc As String
 Set MyRange = Range("E5:E10")
 
 If Not Intersect(Target, MyRange) Is Nothing Then
  MsgBox ("Change made to: " & Target.Address)
  Loc = Target.Value
 End If

Call AcceptVar(Loc)

End Sub

Display More

And on module level:

Code

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 !

rollis13 · May 4th 2022

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.

Worksheet Change event

Participate now!

Share

Similar Threads

VBA to unlock worksheet to allow table row to be added and then protected again

Include worksheet change event automatically

Worksheet change event not working

can any one change this code into worksheet event change macro in general format only

Need a macro on worksheet change event