Posts by 51degreesNorh

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:

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

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 !

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:

    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

Thank you.

Yesterday's thank you seems to have gone AWAL.... your reply was spot on. I just need to study this whole OO thing in VBA more.

Thanks again for your assistance.

Hi,

This happens in a somewhat complex project. At a certain point in time I know what ActiveCell.Address is but I'm not sure what sheet is active.

I'm sure there must be a better way than using the immediate window .....

Any ideas ?

Cheers