Sheet Names

P

PH NEWS

Hi All,

I have an excel sheet which at the moment takes it name from a cell on that
sheet, but what I would like to reverse if possible. How can I get the sheet
to name itself from a cell. I'm sure I've been on a web page in the past
with this info on it, does anyone have the link?

Thanks in advance

SPL
 
M

Max

Perhaps for this line:
.. How can I get the sheet to name itself from a cell
Click to expand...

If the name is coming from the same cell
in every sheet (say: B2),
then this sub* should work
*From a .prog post by Neil

Sub RenameWS()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ws.Name = Range("B2").Value
Next
End Sub
 
M

Max

PH NEWS said:
.. I would like my sheetname to
= a cell, say a1. So if A1 = Smith, John
I want the sheet to have that name.
If A1 then changes to Jones, Paul
I want the sheet name to change to Jones, Paul.
Click to expand...

From a post by Chip Pearson:

".. Put the following code in the worksheet's code module. It will
automatically rename the sheet when cell A1 is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$1" Then
Me.Name = Target.Text
End If
End Sub

... "
 
B

Bob Phillips

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Parent.Name = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

Max

Const WS_RANGE As String = "H10"

Bob, any special reason why it's "H10" <g>
The last time I asked, you said "A1" was boring
 
P

PH NEWS

Once again thanks to all. However I have a slight problem with these codes.
Primarily they all work great, but they only work when the user makes the
entry in the selected cell. Can I make these codes work when the cell at
which the code finds the sheetname is the result of aVLookup??
 
B

Bob Phillips

Same reason <vbg>

My reference code uses A1:H10 as the range, but here I assumed just a single
cell, and A1 is boring, so ...

Best Regards

Bob
 
M

Max

PH NEWS said:
... Can I make these codes work when the cell at
which the code finds the sheetname is the result of aVLookup??
Click to expand...

Try this sub from a post by Tom Ogilvy:
(lightly tested)

Private Sub Worksheet_Calculate()
Application.EnableEvents = False

On Error Resume Next
Me.Name = Me.Range("A1").Value

Application.EnableEvents = True
End Sub
 
M

Max

PH NEWS said:
Thanks, but this didn't seem to work.
Click to expand...

well, Tom's sub worked ok for me over here (in xl97) when tested
with a simple VLOOKUP returning a result in A1 (as per your earlier post)
was this your test condition over there ?
However I've thought of what seems to
be a much simpler answer. I recorded a macro that selects A1 or H10 (which
ever is more exciting), press F2 and then enter. Although I don't know how
to add this to the original codes. Could you help please?
Click to expand...

Probably it might be out of my depth,
but others versed in vba, eg: Bob Phillips
would be able to step in and help out. Post the code.
 
P

PH NEWS

Perhaps I entered it wrong, I really know nothing about VBA. Was that code
in addition to the first one or was it a stand alone routine?
 
M

Max

PH NEWS said:
Perhaps I entered it wrong, I really know nothing about VBA.
Was that code in addition to the first one
or was it a stand alone routine?
Click to expand...

Tom's sub was supposed to replace the earlier sub.
Delete the earlier sub, paste Tom's code in,
then test it out. Think it works ok.
 
Top