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?


--
Cook
------------------------------------------------------------------------
Cook's Profile: http://www.thecodecage.com/forumz/member.php?userid=107
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=59817

dan dungan;217711 Wrote:
How are you getting the tabs to autoname now?

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 Sub


--
Cook
------------------------------------------------------------------------
Cook's Profile: http://www.thecodecage.com/forumz/member.php?userid=107
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=59817

Is the cell formatted as number?, maybe change
Code:
--------------------
Me.Name = Range("b9").Value
--------------------
for
Code:
--------------------
Me.Name = Range("b9").Text
--------------------

Cook;217720 Wrote:
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 Sub


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=59817

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.



Cook wrote:

dan dungan;217711 Wrote:
How are you getting the tabs to autoname now?

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 Sub

--
Cook
------------------------------------------------------------------------
Cook's Profile: http://www.thecodecage.com/forumz/member.php?userid=107
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=59817

--

Dave Peterson

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)


On Fri, 6 Feb 2009 21:22:17 +0000, Cook
wrote:


dan dungan;217711 Wrote:
How are you getting the tabs to autoname now?

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 Sub

You guys are good.... Got it Thanks!!!!


--
Cook
------------------------------------------------------------------------
Cook's Profile: http://www.thecodecage.com/forumz/member.php?userid=107
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=59817

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

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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=59817