How can vba to determine if a row has been inserted or deleted or if a group of cells has been inserted or deleted, changing the worksheet structure?

sricc

New Member
Joined
Nov 21, 2014
Messages
12
How can vba to determine if a row has been inserted or deleted or if a group of cells has been inserted or deleted, changing the worksheet structure?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
You need to log all actions, or to compare the data with a previous state of it.
 
Upvote 0
Can you elaborate a bit on your goal.
 
Upvote 0
Can you elaborate a bit on your goal.
When approval is given for a customer sales quote, a vba procedure locks all entry cells in several sheets within the workbook to prevent further edits. There is frequently occasion to "Renegotiate" the quote which requires unlocking the entry cells again. To do this, I have run a code to map all "unlocked" cells and store their cell addresses in a hidden column. When the user executes "Renegotiate Quote", vba loops through all cell address mapping in the hidden column and unlocks those cells. Problem exists when management sees the need to insert or delete rows to create or remove records. this causes the need to run the mapping program again to list all unlocked cells. I want to cause vba to display a warning to run the mapping program if rows have been inserted or deleted. This would also be necessary for partial, such as cells.insert or cells.delete.
 
Upvote 0
There are unfortunately no specific direct;y exposed events to trigger on inserting or deleting cells.
There is a discussion on a similar topic here: Insert Row "Event"
And probably other places.
However there may be a workaround:
Surround or data with ones in a hidden column and a hidden row.
Whenever a cell/row/column ar inserted or deleted a blank cell will appear in them or their sum/count will change.
You can use this information to get a clue that something has happened - you can use VBA to check them on WorkbookOpen or a formula and conditional formatting.
Non vba solution can be similar to this:
MrExcel Playbook 01 2021-08.xlsm
ABCDEFGH
1FALSE1
21
3Base price$ 100.001
4Base time04:00:00hrs41
5Extra:$ 10.00per 00:10:000.1666671
61
7TripHoursOvertimeAdjusted price1
8Trip 13.50$ 100.001
9Trip 24.170.17$ 110.001
10Trip 34.330.33$ 110.001
11Trip 44.50.5$ 130.001
12Trip 54.670.67$ 140.001
13
14Trip 64.840.84$ 150.001
15Trip 751$ 160.001
16Trip 85.171.17$ 170.001
17Trip 95.331.33$ 170.001
18Trip 105.51.5$ 190.001
19Trip 115.671.67$ 200.001
20Trip 125.841.84$ 210.001
211
221
231
241
251
261
271
281
291111111
Sheet6
Cell Formulas
RangeFormula
A1A1=AND(SUM(INDIRECT("28:28"))=7,SUM(INDIRECT("H:H"))=27)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1Cell Value<>TRUEtextNO
 
Upvote 0
There are unfortunately no specific direct;y exposed events to trigger on inserting or deleting cells.
There is a discussion on a similar topic here: Insert Row "Event"
And probably other places.
However there may be a workaround:
Surround or data with ones in a hidden column and a hidden row.
Whenever a cell/row/column ar inserted or deleted a blank cell will appear in them or their sum/count will change.
You can use this information to get a clue that something has happened - you can use VBA to check them on WorkbookOpen or a formula and conditional formatting.
Non vba solution can be similar to this:
MrExcel Playbook 01 2021-08.xlsm
ABCDEFGH
1FALSE1
21
3Base price$ 100.001
4Base time04:00:00hrs41
5Extra:$ 10.00per 00:10:000.1666671
61
7TripHoursOvertimeAdjusted price1
8Trip 13.50$ 100.001
9Trip 24.170.17$ 110.001
10Trip 34.330.33$ 110.001
11Trip 44.50.5$ 130.001
12Trip 54.670.67$ 140.001
13
14Trip 64.840.84$ 150.001
15Trip 751$ 160.001
16Trip 85.171.17$ 170.001
17Trip 95.331.33$ 170.001
18Trip 105.51.5$ 190.001
19Trip 115.671.67$ 200.001
20Trip 125.841.84$ 210.001
211
221
231
241
251
261
271
281
291111111
Sheet6
Cell Formulas
RangeFormula
A1A1=AND(SUM(INDIRECT("28:28"))=7,SUM(INDIRECT("H:H"))=27)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1Cell Value<>TRUEtextNO
Thanks for the idea! I'll give it a try.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top