Can you name a worksheet by refering to a cell reference or range

  • Thread starter Thread starter o2bing
  • Start date Start date
O

o2bing

I want to have a worksheet's name to have meaning, depending on data entered
in a particular cell reference or range. Is this possible, rather then just
editing the worksheet name manually.
 
If, say cell A1 contains the string to be used as sheet name then

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then ActiveSheet.Name =
Target.Value
End Sub

changes sheet name to string in A1.

Post if you need help to install this event macro!

Regards,
Stefi

„o2bing†ezt írta:
 
I forgot to mention that it happens only when you enter a value in A1, but it
always happens when you enter a value in A1.
Stefi


„Stefi†ezt írta:
 
Hi Stefi,

Sorry about my ignorance, but yes, please assist with installing this macro.
I am guessing I need to be in visual basic, but I'm not sure where the code
needs to be.

Thanks
 
Open Visual Basic editor (Alt+F11 or Tools>Macro> Visual Basic Editor )
Open Project Explorer if it's not open by default (Ctrl+R or View>Project
Explorer)
Right click on sheet you want to apply the event macro to (among Microsoft
Excel Objects)>View code>Copy macro in the code window!
Stefi



„o2bing†ezt írta:
 
Hi,

Entered data into A1. Copied the three lines of code into the code window,
closed visual basic and returned to the spreadsheet, but the worksheet
remains named 'Sheet 1'. Should I be doing something else?

Thanks
 
Action order was wrong:
first step: install macro (now it's done)
second step: typing value in A1

Now retype value in A1 and watch the result!
Stefi


„o2bing†ezt írta:
 
You are welcome! Thanks for the feedback!
Stefi

„o2bing†ezt írta:
 
Stefi

It is much easier to just right-click on the Sheet Tab and "View Code" rather
than going through the Alt + F11 and Project Explorer steps.

Paste the code in and Alt + q to return to the Excel sheet window.


Gord Dibben MS Excel MVP
 
Thanks Gord, it's a nice little trick I wasn't aware of!
Stefi


„Gord Dibben†ezt írta:
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup Reference Certain Worksheets 4
Excel Msgbox runing beyond posted cell range. 1
Use worksheet name in one cell as part of reference in other cells 3
Using cell as worksheet reference (macro) 1
Code to format cells on several worksheets 2
Setting Scope of name ranges to particular worksheet 2
Chart references when copying worksheets 3
Cell reference between worksheets 4

Back
Top