Autonaming tabs

C

Cook

I am looking for a macro to auto name my tabs the contents of cell B9.
The tricking part is the contents of B9 will always have a 0 in front of
it. (0234)

I can get the tab to name itself but it does not bring the 0 with
it....

Any suggestions?
 
C

Cook

dan said:
How are you getting the tabs to autoname now?
Click to expand...

Yes, just need to modify the code to bring the zero over.

Here is what I have so far.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("b9")) Is Nothing Then
On Error Resume Next
Me.Name = Range("b9").Value
If Err.Number <> 0 Then
MsgBox "Quote sheet has not been named"
End If
On Error GoTo 0
End If


End Su
 
S

Simon Lloyd

Is the cell formatted as number?, maybe chang
Code
-------------------
Me.Name = Range("b9").Valu
-------------------
for
Code
-------------------
Me.Name = Range("b9").Tex
-------------------

Cook;217720 said:
Yes, just need to modify the code to bring the zero over

Here is what I have so far

Private Sub Worksheet_Change(ByVal Target As Range

If Not Intersect(Target, Range("b9")) Is Nothing The
On Error Resume Nex
Me.Name = Range("b9").Valu
If Err.Number <> 0 The
MsgBox "Quote sheet has not been named
End I
On Error GoTo
End I


End Su
Click to expand...

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
D

Dave Peterson

If that 0 is added because you used a custom number format, maybe changing this
line:

Me.Name = Range("b9").Value
to
Me.Name = Range("b9").text

would be enough.
 
C

Chip Pearson

Change
Me.Name = Range("b9").Value
to
Me.Name = Range("b9").Text

The Text property returns exactly what is displayed in the cell, with
formatting, while Value gets the underlying, unformatted value.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
S

Simon Lloyd

{share}Glad we could be of help!
{share}*Saying thanks.*

* If you have found my tip helpful then please click on th
animated \\"Say Thanks\\" button to the top right of my post to registe
your appreciation

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
Top