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.

 

[alansidman]

One way would be to place an input box in the Macro to ask what sheet and then make the variable sheet that name. I am sure that there are many other ways. This is only one possibility.

 

[Norie]

You should easily be able to make the code only work on one sheet by adding the appropriate worksheet reference(s) throughout.

By the way, why is the code in the ThisWorkbook module?

That's normally only used for code for workbook level events.

 

[JenniferMurphy]

One way would be to place an input box in the Macro to ask what sheet and then make the variable sheet that name. I am sure that there are many other ways. This is only one possibility.

Are you suggesting an Input Box to ask the user to enter the sheet name each time the macro runs? That would be very inconvenient for the user (me). I want something that works automatically without user intervention.

 

[JenniferMurphy]

You should easily be able to make the code only work on one sheet by adding the appropriate worksheet reference(s) throughout.

If I add worksheet references to every comment, won't that cause the macro to operate on that worksheet regardless of where it is called? I certainly do not want the macro to operate on sheet1 if called from sheet2.

I want the macro to exit, possibly with a message, if called from the wrong sheet. Better yet, I want to prevent it from even being called from any sheet other than the right one.

By the way, why is the code in the ThisWorkbook module?

That's normally only used for code for workbook level events.

I put the macro in Module1 under the workbook containing the sheet. I tried putting it in the sheet, but then it wouldn't run at all.

 

[JenniferMurphy]

After a little more research, I found ActiveSheet.Name. I now have this code, which seems to work:

Const SubName As String = "WinLoss Sub"   'The name of this routine

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

A couple of questions:

1. Is there a better way to do this?
2. ActiveSheet.Name returns the renamed name of the sheet, not the original name (sheet26). Is there a way to get that original name?
3. Is there a way to restrict keyboard shortcuts so that they only work in one sheet and not in the whole workbook?

 

[footoo]

Post the whole macro.

 

[alansidman]

Are you wanting to run this from only the active sheet? ie. If sheet 26 is the active sheet,then it will run on that sheet.

 

[JenniferMurphy]

Post the whole macro.

The macro is under construction, so what I have so far doesn't really work. I'm happy to post it, but I don't see the point. I'm just trying to get the first part working that controls who can call it and what it does if called from a different sheet.

Here's the complete code such as it is. But right now, I am only interested in the first 7 lines.

Sub WinLoss()
Const SubName As String = "WinLoss Sub"   'The name of this routine

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

Const CellNameWins As String = "WinsNew"  'The name of the [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=wins]#wins[/URL]  cell
Const CellNameTS As String = "TSNew"      'The name of the time stamp cell
Const CellNameResult As String = "Result" 'The name of the result text cell
Dim XPos As Long, YPos As Long            'The X & Y positions on the screen in twips (1440/inch)
XPos = 6 * 1440
YPos = 7 * 1440
Dim prompt As String, txt As String, msg As String
prompt = "W = Win, L=Loss, QQ=quit"

Do          'Keep looping until the user quits ("QQ")
  txt = InputBox(prompt, SubName, , XPos, YPos)
  Select Case UCase(txt)
    Case "W"
      Range(CellNameTS).Value = Date + Time                     'Store the date & time
      Range(CellNameWins).Value = Range(CellNameWins).Value + 1 'Count the win
      Range(CellNameResult).Value = "Win"                       'Set text = "Win"
    Case "L"
      Range(CellNameTS).Value = Date + Time   'Store the date & time
      Range("HeaderRow").Offset(1).Insert xlDown, Range("ScoringRow").Copy
      Application.CutCopyMode = False
      Range(CellNameWins).Value = 0           'Reset the win count
      Range(CellNameTS).Value = ""            'Clear the date
      Range(CellNameResult).Value = "Loss"    'Set text = "Loss"
    Case "QQ"
      Exit Do
    Case Else
      msg = "**************************" & vbCrLf & "     Invalid response     " & vbCrLf _
          & "**************************"
      MsgBox msg, vbOKOnly, SubName
      MsgBox "==> Please reenter", vbOKOnly, SubName
  End Select
Loop
    
End Sub

 

[Norie]

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