Auditing question!?

G

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
521
Office Version
  1. 2016
Platform
  1. Windows
Hi All ,

In a huge range of cells where hundreds of cells contain values and many formulas, is there any way that a macro or whatever tool may alerts me in case i want to delete a value in one cell, that other cells formulas depend on the value of this cell and may change all data ?

Any help please ? Thank you .
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Go to Tools - Formula Auditing - Trace Dependents

It will point an arrow to all cells that rely on the chosen cells' data. You can also go to View - Toolbars and click on the formula editing toolbar. Then you will have icon buttons to use.
 
Upvote 0
Dirty as heck, but works in testing.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Target.Formula <> vbNullString <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    Application.Undo
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> NoDependents
    <SPAN style="color:#00007F">If</SPAN> Target.Dependents.Count <> 0 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">If</SPAN> vbNo = MsgBox("This cell has dependents, clear anyway", _
                           vbExclamation + vbYesNo + vbDefaultButton2, _
                           "Dependents!") <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> ExitDoor
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
NoDependents:
    Target.ClearContents
ExitDoor:
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 
Upvote 0
please can you guide me where exactly shall this code be pasted in a new module or the workbook itself ?
 
Upvote 0
To add code to a worksheet:
  1. Right-click the tab for the sheet to which you want to add the code.
  2. Click on View Code... from the popup menu.
  3. Copy and Paste code or
    <ul type=disc>
  4. Pick Worksheet from the left combobox at the top of the code pane
  5. If SelectionChange (the default) is not the correct event, then click the appropriate event from the combobox at the right.
[/list]
 
Upvote 0
OK - didn't intend to spend this much time, but the other was too weak of coding to let it slide. This is a little more robust:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rngCell <SPAN style="color:#00007F">As</SPAN> Range, rngDepends <SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngCell <SPAN style="color:#00007F">In</SPAN> Target.Cells
        <SPAN style="color:#00007F">If</SPAN> rngCell.Formula = vbNullString <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> SomethingCleared
    <SPAN style="color:#00007F">Next</SPAN> rngCell
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
SomethingCleared:
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    Application.Undo
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngCell <SPAN style="color:#00007F">In</SPAN> Target
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> rngDepends = rngCell.Dependents
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
        <SPAN style="color:#00007F">If</SPAN> rngDepends <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            rngCell.ClearContents
        <SPAN style="color:#00007F">ElseIf</SPAN> rngCell.Formula <> vbNullString <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> vbYes = MsgBox("Cell " & rngCell.Address(False, False) & " has dependents, clear anyway", _
                              vbExclamation + vbYesNo + vbDefaultButton2, _
                             "Dependents @ " & rngCell.Address(False, False) & " !") <SPAN style="color:#00007F">Then</SPAN>
                rngCell.ClearContents
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> rngDepends = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> rngCell
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 
Upvote 0
that was great too it asked me if i want to clear anyway !

Thank you both !
 
Upvote 0
Oh there is a small problem! i tried to insert a row after a cell and an error type 13 appeared asking to debug or end , it happened too after clicking undo ! :confused:
 
Upvote 0
In cases like that, you can click on the DEBUG option and step through and see where it's derailing. You may be able to fix it yourself! In the cases you found, it's the UNDO statement that is erroring out. We can work around this by telling it to leave if UNDO errors out. But your discovery about inserting rows causing UNDO to error implies that DELETING rows will likewise cause UNDO to error. And indeed it does. So, this is an Achilles heel to the whole shebang. It may be time to consider protecting the worksheet.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rngCell <SPAN style="color:#00007F">As</SPAN> Range, rngDepends <SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngCell <SPAN style="color:#00007F">In</SPAN> Target.Cells
        <SPAN style="color:#00007F">If</SPAN> rngCell.Formula = vbNullString <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> SomethingCleared
    <SPAN style="color:#00007F">Next</SPAN> rngCell
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
SomethingCleared:
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> RestoreAndLeave
    Application.Undo
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngCell <SPAN style="color:#00007F">In</SPAN> Target
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> rngDepends = rngCell.Dependents
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
        <SPAN style="color:#00007F">If</SPAN> rngDepends <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            rngCell.ClearContents
        <SPAN style="color:#00007F">ElseIf</SPAN> rngCell.Formula <> vbNullString <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> vbYes = MsgBox("Cell " & rngCell.Address(False, False) & " has dependents, clear anyway", _
                              vbExclamation + vbYesNo + vbDefaultButton2, _
                             "Dependents @ " & rngCell.Address(False, False) & " !") <SPAN style="color:#00007F">Then</SPAN>
                rngCell.ClearContents
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> rngDepends = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> rngCell
RestoreAndLeave:
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Listing Months within a range of Dates
Replies
7
Views
187
Peter_SSs
Peter_SSs
R
  • Question
Best approach (VBA) to search through a sheet for all matches to a selected range (ie. multiple cells)?
Replies
2
Views
160
radiocam
R
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
167
MoonDancer
M
S
  • Question
Is it possible to make a period scheduler utilizing the "=SWITCH" functionality, or is there a better more efficient way?
Replies
0
Views
92
Sjacoexcelnovice
S
T
  • Solved
=IF Formula Question
Replies
5
Views
309
Fluff
Fluff

Forum statistics

Threads
1,215,771
Messages
6,126,797
Members
449,337
Latest member
BBV123

Share this page

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