Calling up Sheet Name
Calling up Sheet Name
(OP)
Is there any way I can have, say cell A1 equal the sheet name?
And vise-versa, the sheet name equals cell A1 (not at the same time.
I tried a bit of VBA, but I can only get it to work once (i.e when sheet1 is no longer called sheet1 I run into problems).
Many thanks in advance
And vise-versa, the sheet name equals cell A1 (not at the same time.
I tried a bit of VBA, but I can only get it to work once (i.e when sheet1 is no longer called sheet1 I run into problems).
Many thanks in advance





RE: Calling up Sheet Name
This equation will return the worksheet name...
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
This equation will return the full path name with the worksheet name ...
=RIGHT(CELL("filename"),LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"), ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))
***THESE WILL ONLY WORK IN A SAVED WORKBOOK***
Hope that helps some. I am looking forward to the answer to your second question. Hopefully someone out there knows the answer...
-Kati
RE: Calling up Sheet Name
-Kati
RE: Calling up Sheet Name
CODE
CODE
CODE
where the index value (1 in the example) refers to the sheet on the leftmost tab.
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Calling up Sheet Name
Can you please share how you would utilize these codes. Do you just put them into VSE? Is there another step?
I have seen similar suggestions but never could figure out how to implement it.
Any help would be greatly appreciated.
-Kati
RE: Calling up Sheet Name
Start by adding a command button (View|Toolbars|Control Toolbox shows the toolbox, then click on command button and draw it onto your spreadsheet). Go into Design Mode (click the SetSquare symbol on the Toolbox toolbar)
When you double-click on the new button you will get to the code page and should see this:
CODE
End Sub
Just insert the line of code given previously between the 2 lines shown. You can also get into and out of design mode through ALT-F11
Go back to your spreadsheet view, go out of Design Mode (click the SetSquare symbol on the Toolbox toolbar again) and your button will work
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Calling up Sheet Name
CODE
Sheets(1).Name = IIf(IsEmpty(Range("A7").Value),
"NoName2", Range("A7").Value)
End Sub
Of course the extent of your events that you could pick to precipitate updating the name of the sheet include double click, right click, desctivate, calculate and selection change. (There are more.)
RE: Calling up Sheet Name
Function Sheetname()
Application.Volatile
Sheetname = Application.Caller.Parent.Name
End Function
RE: Calling up Sheet Name
And why would you use Application.Volatile in a function that doesn't reference a Cell?
The current worksheet name is simply:
ActiveSheet.Name
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk