Posts by rollis13

    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.

    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

    You could work on something like this:

    Code
    For Each cell In Sheets("MySheet").Columns(17).Cells
        Select Case cell.Value
            Case "-29.75"
                If cell.Offset(0, -6) = "02-01-2022" Then
                    cell.Offset(0, 2).Value = "-8.33"
                ElseIf cell.Offset(0, -6) = "03-01-2022" Then
                    cell.Offset(0, 2).Value = "-10.50"
                End If
        End Select
    Next

    Have a try with my macro to be pasted in the sheet's module (the one with the dropdowns) and change the date format of the time-stamp if necessary:

    Code
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.EnableEvents = False
        If (Target.Column = 6 Or Target.Column = 8 Or Target.Column = 10) Then Target.Offset(0, 1) = Format(Now, "mm/dd/yyyy hh:mm:ss")
        Application.EnableEvents = True
    End Sub

    Yes that's right; as you may understand, the data is too long or too wide to fit in one page, you must decide, or reduce the hight of the rows or reduce the width of the columns in your sheet. Then you can go back to:

    Code
    .FitToPagesWide = 1
    .FitToPagesTall = 1

    My suggestion without knowing any thing about you project:

    With the help of your Function, before updating column B I would copy the old data from column B into a hidden helper column (maybe somewhere in the sheet, in your example lets say column H) and only then update column B. With a formula in column C compare new data in column B against old data in the hidden column. Formula for column C:


    =IF(B7<>H7,"Delta","")


    If you can't make any changes to your Function just have the copy of the old data done by the macro you have showen above.

    That's because you are activating the sheets Sheet(ws(i)).Activate to fetch the data and the last activated sheet will be the last of the ws Array so it's the "3. ...". It isn't necessary to activate a sheet to fetch data from it; just give the right reference to the ranges used.

    Now, first of all get rid of the trailing spaces in the sheet names, they only create confusion since they aren't visible.

    Then try this:

    Do you mean that every sheet has to create it's own template ? Just get rid of the ws Array and the cycle For i.

    Just set a variable (ws) to read the name of the sheet from which you launch the macro.

    Also I would close the template just after saving it if you don't need it straight away (see note in macro).