Forum Discussion
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 that section,
Everytime a new entry is done in the sheet from where the stacking is happening
This will change the output in target cells, but the vba event is not triggering
If this way vba doesn't work, my next option is to trigger the event sheet change, so that if I go on the different sheet to add rows, automatically the event gets triggered.
How will we do this?
However I want this as my backup option.
I've added the sample sheet for reference.
PeterBartholomew1 SergeiBaklan HansVogelaar
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 Suband placed the instruction to clear contents in a general code module
Sub ClearManualInput() Range("ManualEntries").ClearContents End SubI hope this at least represents a step in the right direction.
4 Replies
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.
- I understand, and I was exploring the declarations about the TableUpdate as well.
In my original file, the gallon and sample are indeed tables, but with a lot of columns, but there's so much happening in every column of those.
Can you help me a TableUpdate macro, in my sample file, so that I can try replicating it?
My VBA knowledge is just surface level.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 Suband placed the instruction to clear contents in a general code module
Sub ClearManualInput() Range("ManualEntries").ClearContents End SubI hope this at least represents a step in the right direction.
