Updating Worksheet Tabs

[cdsaroma]

Hi,

I have worksheet 1 (Attendance) which has names (last, first) in rows 4-20 and columns B-C

On worksheet 2 (client name) I have in cell H5 this formula:

=Attendance!C4&" "&Attendance!B4

which is the Client Name
This part works fine.

I also have code:

Private Sub Workbook_Open()
Application.StatusBar = ""
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$H$5" Then Sh.Name = Target
End Sub

(placed in 'thisworkbook') that names the sheet tab the value of worksheet 2, cell H5, therefore the client name.

My problem.
If I change the client name on sheet one, it updates on sheet 2 but the sheet tab does not update unless I double-click (activate) cell H5 (on sheet 2).

Any ideas how to get this to update automatically when I re-open the sheet?

thanks
Paul

 

[AlphaFrog]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Attendance" Then
        If Not Intersect(Range("B4:C4"), Target) Is Nothing Then
            Sheets(2).Name = Range("C4").Value & " " & Range("B4").Value
        End If
    End If
End Sub

 

[cdsaroma]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Attendance" Then
        If Not Intersect(Range("B4:C4"), Target) Is Nothing Then
            Sheets(2).Name = Range("C4").Value & " " & Range("B4").Value
        End If
    End If
End Sub

Thanks for that but I still still have an issue.
That code is good for sheet 2 (which represents client name in B4/C4, how do I implement it so if I change Attendance B5/C5 it updates sheet 3 etc...?

thanks

 

[AlphaFrog]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim r As Long
    If Sh.Name = "Attendance" Then
        If Target.Count = 1 Then
            If Not Intersect(Range("B4:C10"), Target) Is Nothing Then
                r = Target.Row
                If Len(Range("C" & r).Value & Range("B" & r)) > 0 Then
                    Sheets(r - 2).Name = Range("C" & r).Value & " " & Range("B" & r).Value
                End If
            End If
        End If
    End If
End Sub

You could put this in the "Attendance" worksheet module instead of the ThisWorkbook module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    If Target.Count = 1 Then
        If Not Intersect(Range("B4:C10"), Target) Is Nothing Then
            r = Target.Row
            If r - 2 <= Sheets.Count Then
                If Len(Range("C" & r).Value & Range("B" & r)) > 0 Then
                    Sheets(r - 2).Name = Range("C" & r).Value & " " & Range("B" & r).Value
                End If
            End If
        End If
    End If
End Sub

 

[cdsaroma]

Perfect!
Thanks very much

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim r As Long
    If Sh.Name = "Attendance" Then
        If Target.Count = 1 Then
            If Not Intersect(Range("B4:C10"), Target) Is Nothing Then
                r = Target.Row
                If Len(Range("C" & r).Value & Range("B" & r)) > 0 Then
                    Sheets(r - 2).Name = Range("C" & r).Value & " " & Range("B" & r).Value
                End If
            End If
        End If
    End If
End Sub