×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Calling up Sheet Name
2

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

RE: Calling up Sheet Name

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

RE: Calling up Sheet Name

Oh and I forgot to say that if you change the worksheets name that these equations will automatically update.

-Kati

RE: Calling up Sheet Name

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: www.essexsteam.co.uk

RE: Calling up Sheet Name

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

RE: Calling up Sheet Name

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: www.essexsteam.co.uk

RE: Calling up Sheet Name

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.)

RE: Calling up Sheet Name

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


RE: Calling up Sheet Name

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: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close