Limit Quick Tasks macro to a specific worksheet

[AndyTampa]

I've tried searching here and Google and just can't seem to ask the right way to get the right search results.

I put a personal macro in my Quick Access Toolbar to use on a spreadsheet I use every day. I make a new one every month. When there is too much text in a cell, it merges with the 4 cells next to it and corrects the height automatically.

The problem I have is when I accidentally run the macro (via the Quick Access button) on a different sheet or workbook. The macro runs and there is no "undo". I'm trying to find a way for this button to only work when I am on a particular tab named "Packages Worked" on a workbook that always has "Packages Worked" in the title.

If I'm not on the sheet or it isn't even open, I just want a MsgBox that the macro only works on that particular sheet. I tried the below line, but it got a debug error when the workbook was closed and I was in another workbook.

If ActiveWorkbook.Worksheets("Packages Worked") Is ActiveSheet Then

I don't even have a clue how to check if the workbook name contains specific text. It seems like it would be simple, but searching for the information is getting me all the wrong answers.

 

[rlv01]

You might want to try something like this instead:

 If ActiveSheet.Name = "Packages Worked" Then

 

[mumps]

Give this a try. Place your code where indicated.

Sub checkWorkbook()
    If ActiveWorkbook.Name Like "*Packages Worked*" And Evaluate("isref('" & "Packages Worked" & "'!A1)") Then
        'your code here
    Else
        MsgBox "The macro works only on sheet 'Packages Worked'."
    End If
End Sub

 

[AndyTampa]

Give this a try. Place your code where indicated.

Sub checkWorkbook()
    If ActiveWorkbook.Name Like "*Packages Worked*" And Evaluate("isref('" & "Packages Worked" & "'!A1)") Then
        'your code here
    Else
        MsgBox "The macro works only on sheet 'Packages Worked'."
    End If
End Sub

This works to prevent the macro from working when I'm in the wrong workbook but that's only half the requirement.

If I'm in the right workbook, but on the wrong worksheet of that workbook, the macro still ran. I don't understand the second part of the macro from Evaluate onward. It looks like it's specifying cell A1. I don't know why you would reference a cell. So I changed the second half of the macro to what rlv01 said and it worked for what I needed.

You might want to try something like this instead:

 If ActiveSheet.Name = "Packages Worked" Then

So now I have:

Sub checkWorkbook()
    If ActiveWorkbook.Name Like "*Packages Worked*" And ActiveSheet.Name = "Packages Worked" Then
        'your code here
    Else
        MsgBox "The macro works only on sheet 'Packages Worked'."
    End If
End Sub

I don't think I can mark both as the solution though or else I would. I guess I have to mark my own post as the solution to give you both credit.

 

[mumps]

Glad it worked out.

I don't know why you would reference a cell

This part of the code checks to see if the worksheet actually exists in the workbook.