Automatic Worksheet Name Displayed In Cell

[FRIJOE]

Good Afternoon,

Can anyone please assist in displaying the tab name in a cell value. I need to tab name to automatically update without having to manually run any macros.

I've tried using the following equation

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

which works... however I notice whenever I, or another user, start the workbook the cell's value appears as "#VALUE!". So I've turned to VBA to automatically update the value of the cell. I have a working code that updates the value of cell "AU9" however I have to manually run it each time. Is there a way to have this code automatically display the value? Ideally I would like to change the tab name and see the changes immediately as the value displayed is the driving force for a series of vlookups and data validations in another tab.

Private Sub TabName()
Dim wsname As String
wsname = ActiveSheet.name
Range("au9") = wsname
End Sub

 

[offthelip]

Yes you can call your sub in the Worksheet activate event handler. This will call your subroutine everytime the worksheet is activated.
To do this from the VBA project window right click on the the sheet where you want this and select view code. In the lefthand box select worksheet and then in the right hand box select "activate" then post yout code into the box so you get:

Private Sub Worksheet_Activate()
Dim wsname As String
wsname = ActiveSheet.Name
Range("au9") = wsname
End Sub

 

[FRIJOE]

Yes you can call your sub in the Worksheet activate event handler. This will call your subroutine everytime the worksheet is activated.
To do this from the VBA project window right click on the the sheet where you want this and select view code. In the lefthand box select worksheet and then in the right hand box select "activate" then post yout code into the box so you get:

Private Sub Worksheet_Activate()
Dim wsname As String
wsname = ActiveSheet.Name
Range("au9") = wsname
End Sub

offthelip, I appreciate the response. Your code works but I need to leave the tab and come back to it for the changes to take place. Is there any way to have the changes automatic where as soon as the tab name is changed, the sub changes too?

 

[FRIJOE]

Disregard.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  Application.EnableEvents = False
    Dim wsname As String
    wsname = ActiveSheet.Name
    Range("au9") = wsname
  Application.EnableEvents = True
End Sub

This works every time I select another cell.

 

[Norie]

Press F9 so the formula recalculates?

 

[offthelip]

I was just about going to suggest exactly that!!