macro to automatically date sheet tab

[Jackie]

Hi



I have found this macro that automatically names the tab for a sheet. Does anyone know how to change the macro to allow a date as a tab name?

Thank you..............complete macro novice

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(Target, 31)
Exit Sub
Badname:
MsgBox "Please revise the entry in A1." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
End Sub

[gmayor]

Which date do you want to use and when do you want to use it?

[Jackie]

Hi
I want the tab date to be the manually entered date in cell A1.

So I've made the workbook with 4 tabs representing 4 weeks of info. The tabs are originally named 1, 2, 3, 4. So once the workbook is in use, the date entered in cell A1 is the date I want the tab named.

Thankyou

[Jackie]

My spreadsheet is a diary that staff enter their booked hours of working for a 4 week period. So on the 2nd tab is a table that holds the 4 week commencing dates (and other info irrelevant to this macro), the 3rd sheet is the 1st wc diary and so on until the 5th sheet. ok, so I've worked on my spreadsheet some more and have found the macro to date a tab.
The tab dates come from the cell E3 that are populated from sheet 1 which holds the 4 wc dates I want for the 4 week diary.

The tab dates work fine, except I have to click in a cell on each sheet to make the tab change date name. Is there anyway that the tab date names can change by just entering the dates in sheet 1......thank you

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Set Target = Range("E3")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Format(Target, "dd-mm-yy")
Exit Sub
Badname:
MsgBox "Please revise the entry in E3." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("E3").Activate
End Sub

[NoSparks]

If Graham doesn't mind, and I follow what you're doing and I'm not sure I do...

You only need to use the Worksheet_Change event of the sheet containing the 4 dates.
You will need to refer to the other sheets by their "code name" in order to always be working with the same sheet.
Your dates must be entered manually or with VBA. They cannot be the result of a formula.

[Jackie]

Brilliant......works a treat Thanks loads......oh not quite

[Jackie]

sorry.........does not bring the dates upon my workbook tabs quite right

[NoSparks]

My crystal ball seems to be out-of-order right now so you'll need to tell me what "not quite right" means.

[gmayor]

Originally Posted by NoSparks

If Graham doesn't mind....

Why would I mind?. This is a user peer group forum, we all benefit from the ideas of others. I didn't have time yesterday to get back to this, but my solution would have been similar to yours, and I too am interested to learn what the new problem is.

[NoSparks]

Thanks Graham, appreciate the comment.
I put that statement in because I just hate the one-upsmanship games that seem so prevalent on the "big" forums.

[Jackie]

I have attached my work book.

I would like help with my macro so the tabs date automatically when sheet 2 cell B5 has a date entered. At the moment I have to click on a cell in each sheet before the tab dates change/show

On sheet 2 there is a table of dates.

I would like sheet 3 tab to show the date from cell B5 from sheet 2

I would like sheet 4 tab to show the date from cell B6 from sheet 2

I would like sheet 5 tab to show the date from cell B7 from sheet 2

I would like sheet 6 tab to show the date from cell B8 from sheet 2

Many Thanks

[gmayor]

Your workbook is password protected!

Maybe something like

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("B5:B8")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    If IsDate(Range("B5")) Then
        Sheet3.Name = Format(Range("B5"), "dd-mm-yy")
    End If
    If IsDate(Range("B6")) Then
        Sheet4.Name = Format(Range("B6"), "dd-mm-yy")
    End If
    If IsDate(Range("B7")) Then
        Sheet5.Name = Format(Range("B7"), "dd-mm-yy")
    End If
    If IsDate(Range("B8")) Then
        Sheet6.Name = Format(Range("B8"), "dd-mm-yy")
    End If
End Sub

in the Sheet 2 code. This will update all the sheet names requested when one of the dates is changed.

[NoSparks]

Also remove all the Worksheet_SelectionChange macros that I'm guessing you have on all the sheets.

[Jackie]

Thank you guys for all your patience. Sorry the protection was on but there isn't a password on it. The macro works marvellous and I've removed the original macros in the workbook/sheets.

Thank you Thank you