CODEWITHSUNDEEP
vbaexcel
slomo
slomo

Reputation: 117

Find the macro that affects a particular cell

I'd be happy to get help from the experts:

How can I find all the commands, models and forms that affect a particular cell?

It will not help to look for the cell in the macro, because there is a range name that includes this cell.

I would be happy to receive information / directions / concepts.

If it is not clear enough please write to me.

Upvotes: 0

Views: 230

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37480

If you want to capture macro that modified the values try below code:

Public macroName As String

Sub SomeMacro()
    'store the name in global variable
    macroName = "SomeMacro"
    Cells(1, 1).Value = "new value!"
End Sub

Sub AnotherMacro()
    'store the name in global variable
    macroName = "AnotherMacro"
    Cells(1, 1).Value = "other new value!"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ' here you check what cell was modified,
    ' if it's not the one interesting us, exit sub
    If Target.Address <> "$A$1" Then Exit Sub

    MsgBox "Cell A1 modified by " & macroName
End Sub

You simply store name of executing macro in global variable (which you must set at beginning of every macro) and in event of change, you have information what macro just modified cell of interest.

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57743

The problem is that there is no general way to find all things that affect a particular cell automatically.

If there are only a view cells to test you can use the Worksheet.Change Event with a Stop command on the target cell.

Example:
This stops when the value of range A2 is changed by a macro or user interaction

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then Stop
End Sub

Then you can run your macro to test if it interacts with that cell. It will stop at the event. Then you can use F8 to go step by step and find out which code triggered the change event. But this will only trigger value changes but not changes on format etc.

Upvotes: 2

Related Questions