Re: Tab Names Changes

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Put the work*** change into the info work***. But you'll have to use the
codename of the work***:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "a1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
sheet99.Name = format(Target.Value + 7, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

If you go into the VBE, select your project, select the *** that changes name,
then hit F4 (to see the properties, you'll see a "(name)" property. Use that
name.



Big Rick wrote:
>
> Thanks Dave,
> This does work better but I would like to take it one stage further.
> What I would like is to have a formula in J2 (e.g. Info!A1+7) with Info!A1 =
> 1/1/06.
> Now if Info!A1 were to change, could I have the tab name change automatically.
> By the way, I really am clueless when it come to macros. Remember my post on
> date formatting last week which started a great debate !) So please can you
> answer in laymans terms.
>
> Thanking you in anticipation.
> --
> Big Rick
>
> "Dave Peterson" wrote:
>
> > Worksheet_change waits for you to type something. (Your code checks to see if
> > you typed something in J3--if not, it doesn't do anything.)
> >
> > Since J3 is a formula, it's not changed by typing.
> >
> > You could use the worksheet_calculate event, but I think I'd just start looking
> > at J2:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Const WS_RANGE As String = "j2"
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > With Target
> > Me.Name = format(Target.Value, "dd mm")
> > 'me.name = target.text
> > End With
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > You could use Target.Text if you formatted that cell the way you wanted.

--

Dave Peterson
.

Quantcast