Re: Tab Names Changes

Thank you very much again.
I suppose you really are quite clever when I think about it.
--
Big Rick


"Dave Peterson" wrote:

> You could do it that way. But another way is to go through the codenames and
> set them that way:
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "a1"
>
> Dim iCtr As Long
> Dim wks As Work***
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> For Each wks In Me.Parent.Worksheets
> For iCtr = 1 To 52
> If LCase(wks.CodeName) = "***" & iCtr Then
> wks.Name = Format(Target.Value + (7 * iCtr), "dd mm")
> Exit For
> End If
> Next iCtr
> Next wks
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> ==============
> But this doesn't sound like something that you'd do very often--once a year when
> you're setting things up.
>
> I think I'd remove it from the worksheet_change event and just make a macro that
> runs on demand. It would stop user errors (overwriting A1 on Info could cause
> trouble).
>
> If you think that's a good idea and you have trouble converting it, just post
> back. I'm sure you'll get help.
>
>
>
>
> Big Rick wrote:
> >
> > Thank you, thank you, thank you, so much.
> > Absolutely wonderful. Works like a dream. Please can I ask just one more
> > question though.
> > If I wanted this on 52 worksheets in one workbook +7, +14, +21 etc
> > would I require 52 lines of
> > sheet1.Name = format(target.Value+7,"dd mm")
> > sheet2.Name = format(target.Value+14,"dd mm") etc
> > or is there an easier way.
> > Thank you again for all your time and effort. I really do appreciate it.
> > --
> > Big Rick
> >
> > "Dave Peterson" wrote:
> >
> > > 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.
> > >
>
> --
>
> Dave Peterson
>
.