Reference sheet name in code

[Shadkng]

Hi, the code below refers to sheet "quote", however I need it to reference the active sheet name because I sometimes change the name to something other than quote. Or it can reference the sheet name in cell C1. This cell has the following formula (=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255) if it will have any bearing on this option. Thanks for the help.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    If IsEmpty(Sheets("QUOTE").Range("L9").Value) = False Then
    On Error Resume Next
    If Sh.Name = "TABLE PROD" Then
        MsgBox "NOTES:  " & Worksheets("quote").Range("L9")
    End If
    End If

End Sub

 

[Fluff]

The simplest way would be to use the sheet codename. In the Project window of the editor you should see a list of sheets like

What is the name to the left of the Quote sheet?

 

[Shadkng]

The name is Sheet14 (QUOTE).

What will happen if I duplicate the sheet and rename it? Will this solution carry over?

 

[Fluff]

No it wont, it will always look at sheet14. Is that what you want?

 

[Shadkng]

No it wont, it will always look at sheet14. Is that what you want?

No, I need it to work on duplicated sheets. Can we use an indirect to cell C1? Because that cell contains the name of the current tab.

 

[Fluff]

As C1 just contains the name of that sheet, why not use Activesheet instead of the sheet name?

 

[Shadkng]

As C1 just contains the name of that sheet, why not use Activesheet instead of the sheet name?

OK I'll try that...can you re-write with the correct syntax. Thanks

 

[Fluff]

Try

If ActiveSheet.Range("L9").Value = "" Then

 

[Shadkng]

Try

If ActiveSheet.Range("L9").Value = "" Then

[/
[/QUOTE]

Try

If ActiveSheet.Range("L9").Value = "" Then

I am realizing what I want to achieve is not possible as "table prod" needs to point to a certain named sheet. Once I move to sheet "table prod" then that becomes the active sheet. I would probably need a field in 'table prod" to refer to the sheet I want. Thanks for your help.