Renaming sheet names depending on file name

[Tej92]

Hello everyone, I'm trying to rename the sheet1 to the first 2 words of the file name. I've managed to do it by the following formula in cell C1 to get the file name:=LEFT(MID(CELL("filename"),FIND("[",CELL("filename"))+1, FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1),FIND(".",MID(CELL("filename"),FIND("[",CELL("filename"))+1, FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1))-1)

then this formula to get the first 2 words:=TRIM(LEFT(C1, FIND("^",SUBSTITUTE(C1, " ", "^",2)&"^")))

and then the following VBA code to rename the sheet:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("AD1")) Is Nothing Then

ActiveSheet.Name = ActiveSheet.Range("AD1")

End If

End Sub


But it doesn't change the sheet name unless i go to AD1 and click enter. the value in AD1 is correct as soon as i open the file but it doesn't change the sheet name unless enter is pressed inside the formula tab.
I'd like it to change the name of the cell with out that extra step. any idea?

 

[Tej92]

@Fluff that formula in AD1 works as it should but the formula for C1 is also giving the file extension. that's the formula i found online works fine even though it's quite long. So far the performance of the sheet is not altered.
thank you for the help!!

 

[Joe4]

@Fluff, might it have something to do if their Windows setting is toggled to show/not show file extensions?

 

[Fluff]

Missed the fact you don't want the extension, so it should be

=TEXTBEFORE(TEXTAFTER(CELL("filename",A1),"["),".",-1)

 

[Tej92]

that is a lot shorter of a code and works perfectly!!! thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.