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

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

Stefi

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

Stefi

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

o2bing

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
 
S

Stefi

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

o2bing

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
 
S

Stefi

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

Gord Dibben

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
 
S

Stefi

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


„Gord Dibben†ezt írta:
 
Top