How to log all actions?You need to log all actions, or to compare the data with a previous state of it.
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.Can you elaborate a bit on your goal.
MrExcel Playbook 01 2021-08.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | FALSE | 1 | ||||||||
2 | 1 | |||||||||
3 | Base price | $ 100.00 | 1 | |||||||
4 | Base time | 04:00:00 | hrs | 4 | 1 | |||||
5 | Extra: | $ 10.00 | per | 00:10:00 | 0.166667 | 1 | ||||
6 | 1 | |||||||||
7 | Trip | Hours | Overtime | Adjusted price | 1 | |||||
8 | Trip 1 | 3.5 | 0 | $ 100.00 | 1 | |||||
9 | Trip 2 | 4.17 | 0.17 | $ 110.00 | 1 | |||||
10 | Trip 3 | 4.33 | 0.33 | $ 110.00 | 1 | |||||
11 | Trip 4 | 4.5 | 0.5 | $ 130.00 | 1 | |||||
12 | Trip 5 | 4.67 | 0.67 | $ 140.00 | 1 | |||||
13 | ||||||||||
14 | Trip 6 | 4.84 | 0.84 | $ 150.00 | 1 | |||||
15 | Trip 7 | 5 | 1 | $ 160.00 | 1 | |||||
16 | Trip 8 | 5.17 | 1.17 | $ 170.00 | 1 | |||||
17 | Trip 9 | 5.33 | 1.33 | $ 170.00 | 1 | |||||
18 | Trip 10 | 5.5 | 1.5 | $ 190.00 | 1 | |||||
19 | Trip 11 | 5.67 | 1.67 | $ 200.00 | 1 | |||||
20 | Trip 12 | 5.84 | 1.84 | $ 210.00 | 1 | |||||
21 | 1 | |||||||||
22 | 1 | |||||||||
23 | 1 | |||||||||
24 | 1 | |||||||||
25 | 1 | |||||||||
26 | 1 | |||||||||
27 | 1 | |||||||||
28 | 1 | |||||||||
29 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||
Sheet6 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | A1 | =AND(SUM(INDIRECT("28:28"))=7,SUM(INDIRECT("H:H"))=27) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A1 | Cell Value | <>TRUE | text | NO |
Thanks for the idea! I'll give it a try.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
A B C D E F G H 1 FALSE 1 2 1 3 Base price $ 100.00 1 4 Base time 04:00:00 hrs 4 1 5 Extra: $ 10.00 per 00:10:00 0.166667 1 6 1 7 Trip Hours Overtime Adjusted price 1 8 Trip 1 3.5 0 $ 100.00 1 9 Trip 2 4.17 0.17 $ 110.00 1 10 Trip 3 4.33 0.33 $ 110.00 1 11 Trip 4 4.5 0.5 $ 130.00 1 12 Trip 5 4.67 0.67 $ 140.00 1 13 14 Trip 6 4.84 0.84 $ 150.00 1 15 Trip 7 5 1 $ 160.00 1 16 Trip 8 5.17 1.17 $ 170.00 1 17 Trip 9 5.33 1.33 $ 170.00 1 18 Trip 10 5.5 1.5 $ 190.00 1 19 Trip 11 5.67 1.67 $ 200.00 1 20 Trip 12 5.84 1.84 $ 210.00 1 21 1 22 1 23 1 24 1 25 1 26 1 27 1 28 1 29 1 1 1 1 1 1 1 Sheet6
Cell Formulas Range Formula A1 A1 =AND(SUM(INDIRECT("28:28"))=7,SUM(INDIRECT("H:H"))=27)
Cells with Conditional Formatting Cell Condition Cell Format Stop If True A1 Cell Value <>TRUE text NO