Target.Address to specific Worksheet

[GreenyMcDuff]

Hi all,

This seems like a really easy question but I can't find an answer on google

I am using this code

Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$H$2" Then
        Run
        MsgBox ("Report Compiled Successfully")
    End If
End Sub

To start my macro when someone clicks on Cell H2.

This code has to be maintained in a module and so I now need the macro to run when Cell H2 is click in the Worksheet "Data" only.

I figured using this would work:

Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "[COLOR=red]Data![/COLOR]$H$2" Then
        Run
        MsgBox ("Report Compiled Successfully")
    End If
End Sub

But it does not,

Thanks for all your help :D

Chris

 

[VoG]

If you put that code in Data's code module (right click Data tab, View Code) it will only rin when H2 is selected in the Data sheet.

 

[GreenyMcDuff]

Thanks for your reply Peter.

Unfortunately, I can't do that because I can't have any code in the sheet when the macro has finished.

The Macro copies all the worksheets into a new workbook, and unfortunately if this code is attached to the "Data" sheet it will get copied across as well.

Don't suppose you have any other thoughts???

 

[VoG]

Try in the ThisWorkbook module

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "Data" Then Exit Sub
'
'rest of code
'
End Sub

 

[GreenyMcDuff]

And that's why you are the MVP

Thanks