Forum Discussion

Nishkarsh31's avatar
Nishkarsh31
Brass Contributor
Aug 23, 2022
Solved

Can VBA Event "Change" work if the target address cell is a formula that changes dynamically?

I have data coming in from different sheets using vstack and a target cell, which caluclates the total vstack rows using ROWS(A2#) On "Today" sheet, I have some manual entires, I want to clear th...
excel
Formulas and Functions
Macros and VBA
office 365
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Aug 23, 2022

    Nishkarsh31 

    I wouldn't count myself as a VBA expert either!

    What I have tried is to move the event handler to the 'Gallons' Sheet:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Worksheets("Gallons").ListObjects("Table1").Range) Is Nothing Then ClearManualInput

End Sub

    and placed the instruction to clear contents in a general code module

    Sub ClearManualInput()

Range("ManualEntries").ClearContents

End Sub

    I hope this at least represents a step in the right direction.

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 23, 2022

Nishkarsh31 

The point may be that the evaluation of worksheet formulae does not trigger change events.  It is user input and changes made by VBA that will do that.  The worksheet changes that you can trap are those made to the tables on the 'Gallons' and 'Sample' sheets (formally converting them to Excel Tables might improve the workbook usability)

 

The other part of your query would involve SheetSelectionChange which is a method within the module ThisWorkbook.

Resources