How can I restrict a macro to only work in one sheet?

[JenniferMurphy]

I already have the code in workbook module, but I have assigned it to a keyboard shortcut (Ctrl+d) and that works in all of the sheets in that workbook.

What's the recommended way to restrict the macro so that it only runs when called from one sheet?

Can I restrict the shortcut to just one sheet?

If not, can I query the sheet name in the macro and if not the right sheet, either just exit or issue a warning message?

If I do that, should I use the raw sheet name (sheet26) or the name I have assigned to it. I am assuming the raw name is safer and will still work if I should happen to rename the sheet.

Thanks for any suggestions.

 

[JenniferMurphy]

In that code wherever you have Range it will refer to a range on the active sheet, not any specific sheet.

Yes, I understand. That's why I want to make sure that the macro won't run if called from any other sheet.

 

[footoo]

ActiveSheet.Name returns the renamed name of the sheet, not the original name (sheet26). Is there a way to get that original name?

ActiveSheet.CodeName

 

[Norie]

Which sheet do you want the code to run on and which sheet do you want to run it from?

 

[RoryA]

I would use the code name:

If Not activesheet is sheet1 Then
   msgbox "Wrong sheet active!"
   Exit Sub
End If

for example.

 

[JenniferMurphy]

ActiveSheet.CodeName

Thank you.

 

[JenniferMurphy]

Which sheet do you want the code to run on and which sheet do you want to run it from?

I want it to run from a particular sheet (eg sheet13) and to run on that same sheet. I think I have it. See me response to RoryA below.

 

[JenniferMurphy]

I would use the code name:

If Not activesheet is sheet1 Then
   msgbox "Wrong sheet active!"
   Exit Sub
End If

for example.

That was my initial thinking. That way, if I change the name of the sheet, I don't have to remember to change the macro. But after more consideration, I have decided to use the name. I frequently make a copy of a sheet during development so I can go back if I don't like the changes. Depending on which way the copy goes, that could result in the active sheet having a different codename. For now, I'm going with the name.

. . .

Const SheetName As String = "1 loss"      'The name of the sheet that can use this sub
If ActiveSheet.Name <> SheetName Then
  MsgBox "This macro can only be called from '" & SheetName & "'", vbOKOnly, SubName
  Exit Sub
End If

. . .

Thanks to everyone for the help. It seems to be working until the next time I break it.