Change Sheet Name

[spydey]

Trying to figure out why this isn't working.

When I change B2 to any number, it works fine and the sheet name is change to the corresponding number.

But if I clear out the contents to B2 to blank, the sheet name stays the same as the previous number that was in B2. I would like it to change to "Template" (no quotes)

Any ideas? I feel like it is blatantly obvious ......

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False


Set Target = Range("B2")


If Len(Target.Value) = 0 Then
    Application.ActiveSheet.Name = "Template"
        ElseIf IsNumeric(Target.Value) = True Then
            Application.ActiveSheet.Name = Target
End If
    
Application.EnableEvents = True


End Sub

Thanks a million!

-Spydey

 

[Joe4]

Please post the VBA code that you currently have that is naming the sheet.

 

[spydey]

Please post the VBA code that you currently have that is naming the sheet.

Sorry Joe. I had made my original post and then realized I didn't include the code. I hit edit immediately and added the code. I bet that you snagged my post while I was editing it, didn't see the code, and requested it.

I apologize.

The code was added promptly after I initially posted my inquiry.

Thanks for the response. Hopefully it is something simple that I am just over looking.

Take care!!

-Spydey

 

[cerfani]

that code doesnt validate what cell is being edited so it iappears it will be called whenever the sheet is edited. Target I dont think is meant to ever be set. It is meant for you to reference what cell(s) have been edited.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or Intersect(Target, Range("B2")) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    If IsEmpty(Target.Value) Then
        Application.ActiveSheet.Name = "Template"
    Else
        If IsNumeric(Target.Value) Then Application.ActiveSheet.Name = Target
    End If
    Application.EnableEvents = True
End Sub

something like that

 

[spydey]

that code doesnt validate what cell is being edited so it iappears it will be called whenever the sheet is edited. Target I dont think is meant to ever be set. It is meant for you to reference what cell(s) have been edited.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or Intersect(Target, Range("B2")) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    If IsEmpty(Target.Value) Then
        Application.ActiveSheet.Name = "Template"
    Else
        If IsNumeric(Target.Value) Then Application.ActiveSheet.Name = Target
    End If
    Application.EnableEvents = True
End Sub

something like that

Thank you Cerfani!

I will give that a try.

I see what you mean by the original code I had was not validating which cell was being edited. Although when ever I changed B2 to a numeric value, it did change the sheet name. The issue was that when I cleared out B2 the sheet name was not changed back to what I needed.

Thanks again!

-Spydey

 

[dfsobral]

Trying to figure out why this isn't working.

When I change B2 to any number, it works fine and the sheet name is change to the corresponding number.

But if I clear out the contents to B2 to blank, the sheet name stays the same as the previous number that was in B2. I would like it to change to "Template" (no quotes)

Any ideas? I feel like it is blatantly obvious ......

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False


Set Target = Range("B2")


If Len(Target.Value) = 0 Then
    Application.ActiveSheet.Name = "Template"
        ElseIf IsNumeric(Target.Value) = True Then
            Application.ActiveSheet.Name = Target
End If
    
Application.EnableEvents = True


End Sub

Thanks a million!

-Spydey

hy

i copy your code to a blank sheet and worked perfectly

try add

Debug.Print Len(Target.Value)

after

Set Target

and see if 0 appears in the immediate window

 

[dfsobral]

I see what you mean by the original code I had was not validating which cell was being edited.
-Spydey

if you don't , it always will rename to the cell B2 value (if is numeric)

the problem can't come from that