Tab name change issue

[cpg84]

Hi All,
As a follow on from my previous post a few weeks ago, this is the code I have at the moment. It works perfectly when I change the value in cell G7.

What I didn't account for was when you clear the contents of G7.

I want to be able to either have the tab name to stay as it was before G7's contents was cleared, or change to some other name (which I can specify in another cell that won't change its value).

I hope I have explained this well enough.

Thanks

 

[bebo021999]

like this?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "G7" Then Exit Sub ' check if cell G7 changed
If Evaluate("=ISREF('" & Target.Value & "'!A1)") Then Exit Sub ' check if sheet exists
ActiveSheet.Name = Target.Value
End Sub

 

[bebo021999]

Edit:
add G7="" to exit sub:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "G7" Or Target.Value = "" Or Target.Count > 1 Then Exit Sub ' check if cell G7 changed
If Evaluate("=ISREF('" & Target.Value & "'!A1)") Then Exit Sub ' check if sheet exists
ActiveSheet.Name = Target.Value
End Sub

 

[cpg84]

I think I have done something wrong here. It doesn't like the code for some reason.

 

[Akuini]

@cpg84
Try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.CountLarge > 1 Then Exit Sub

If Target.Address(0, 0) = "G7" And Target.Value <> "" Then
    On Error Resume Next
    If Not Evaluate("ISREF('" & Target.Value & "'!A1)") Then
        If Err.Number > 0 Then
        On Error GoTo 0
            MsgBox "Invalid sheet name" 'in case the new name doesn't meet a sheetname criteria, for example: it has ? or \ in it
        Exit Sub
        End If
            ActiveSheet.Name = Target.Value
    Else
        MsgBox "Sheet already exists"

    End If
End If
End Sub

 

[cpg84]

Looks like that works.

Thank you both

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.