CODEWITHSUNDEEP
excelvbaevents
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

How to pass on the Target to another macro in Excel events?

How to pass on the Target to another macro in Excel events? For just the elegant purpose, I would like to use the following structure, which does not work now.

Private Sub Worksheet_Change(ByVal Target As Range) GoToAnotherMacro(Target) End Sub
Sub GoToAnotherMacro(Target) 'here my code where I can use Target i.e: MsgBox "value: " & Target.Value End Sub

Upvotes: 2

Views: 4138

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12665

I would suggest the use of a global variable:

1) Define a global variable:

Dim RangeSupp As Range

2) In your procedure, pass the local Target to your global variable

Private Sub Worksheet_Change(ByVal Target As Range)
    Set RangeSupp = Target
    GoToAnotherMacro RangeSupp
End Sub

3) Enjoy your target in your other macro

Sub GoToAnotherMacro(Target As Range)
MsgBox Target.Value
End Sub

NOTE: Global variables are put on top of all the code, outside any other Function or Sub. The global variable is preserved in the stack even outside the single scope of the macro. That is the reason why you will be able to pass a global variable from a macro to the other. On the other hand, now you're not able (you get "Object required error") because the Target object, once outside its scope in Worksheet_Change, is removed from the stack so it gets back to its default value (Nothing), causing the problem you have in your above code.

Upvotes: 4

Related Questions