Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calling up Sheet Name 2

Status
Not open for further replies.

iken

Mechanical
May 13, 2003
151
NZ
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
 
Replies continue below

Recommended for you

I can answer the first part...

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
 
Oh and I forgot to say that if you change the worksheets name that these equations will automatically update.

-Kati
 
You can assign a name to the currently active sheet thus:
Code:
ActiveSheet.Name = Range("A6").Value
The method will fail if A6 is empty so you should test for that:
Code:
ActiveSheet.Name = IIf(IsEmpty(Range("A6").Value), "NoName", Range("A6").Value)
If you want to change the name of another sheet, you can use the index of the Sheets collection:
Code:
Sheets(1).Name = IIf(IsEmpty(Range("A7").Value), "NoName2", Range("A7").Value)

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:
 
johnwm-

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
 
The line of code would be an event triggered by something on your spreadsheet. For example you could put a command button on your spreadsheet and run it from there.

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:
Private Sub CommandButton1_Click()

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:
 
It might make more sense to have it reset the name of the sheet every time you visit the sheet. An update button is a good idea, however, as you'd need to be on the sheet to rename it.

Code:
Private Sub Worksheet_Activate()
   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.)
 
Here is the correct way to show a sheet name using VBA. Paste this into the module area on your worksheet (VBA)

Function Sheetname()
Application.Volatile
Sheetname = Application.Caller.Parent.Name
End Function


 
Could you clarify how this works? In what circumstances does the Caller property have a Parent property?

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:
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Top