Forum Discussion

CharlesCTC's avatar
CharlesCTC
Copper Contributor
Jan 23, 2024

Excel 365 VBA coding

Good day.  I have a VBA code on an excel file where if C3 = Yes, then it returns in Cell D3 the Date and Time....From what I understand, VBA doesn't work in Excel 365 online.  When I do it as a formula in the cell, it works the 1st time I have the function string open, but then It doesn't work again on the other cells that might have the same type of formula (different cell look-up's), I am looking for a solution where if anyone opens up the spreadsheet, updates anything in column C to Yes, it will auto populate the date and time in Column D without them having to open up the function and then hit enter on the function. 

Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 23, 2024

    CharlesCTC 

    In Excel 365, VBA (Visual Basic for Applications) can still be used, but it's correct that certain features might be limited in the online version compared to the desktop version. In your case, if you want to automate the process of populating the date and time in Column D based on the value in Column C without manually entering a formula or pressing Enter, you can use a Worksheet Change event in VBA.

    Here's an example VBA code that you can use in your Excel file:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range

    ' Define the range where changes will trigger the date and time entry
    Set rng = Range("C:C")

    ' Check if the changed cell is within the specified range
    If Not Intersect(Target, rng) Is Nothing Then
        Application.EnableEvents = False ' Disable events to prevent infinite loop

        ' Loop through each changed cell in the specified range
        For Each cell In Intersect(Target, rng)
            ' Check if the value in the corresponding row of Column C is "Yes"
            If UCase(cell.Value) = "YES" Then
                ' If "Yes", populate the corresponding cell in Column D with the current date and time
                cell.Offset(0, 1).Value = Now
            Else
                ' If not "Yes", clear the corresponding cell in Column D
                cell.Offset(0, 1).ClearContents
            End If
        Next cell

        Application.EnableEvents = True ' Enable events again
    End If
End Sub

    Now, whenever someone changes a value in Column C, the corresponding cell in Column D will be updated with the current date and time automatically. If the value in Column C is not "Yes," the corresponding cell in Column D will be cleared.

    Remember to save your workbook in a format that supports macros (e.g., .xlsm) for the VBA code to work.

    If this doesn't help you, I recommend adding more information. Information such as the code itself, Excel version, operating system, storage medium, file extension, etc.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources