F2 + Enter Macro

[The Great Dane]

Hi all,

I’m new to this so forgive me -

I need a macro to automatically F2 and Enter on one cell for each tab (Cell A1). The reason I ask is that I have a working macro that renames each tab based on the contents of this cell, A1. However to activate the change in tab name I have to click into A1 and enter for the change to take place - I want this to be automatic.

Thanks,

 

[fadee2]

so, by tab do you mean "worksheet tabs" and also, what is your piece of code?

 

[GWteB]

Hi & welcome to MrExcel.

However to activate the change in tab name I have to click into A1 and enter for the change to take place

This sounds like a chicken and egg story: what came first?

So what comes first, changing cell A1 or changing the name of your worksheet?
Imo you don't need a "F2 & Enter" approach. Tell us exactly what you want and supply the code you already have.

 

[The Great Dane]

Hi both,

Thanks for the fast replies.

I can’t share the cut and paste code but I have written it here:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range(“A1”)) Is Nothing Then
ActiveSheet.Name=ActiveSheet.Range(“A1”)
End if
End Sub

The idea is that A1 updates based on a data sample (so this is a formula inside A1) so once the dataset is inserted, A1 would change, then the macro triggers the worksheet name to change as currently the worksheet name does not update unless I physically go into A1 each time.

To answer your question, I’m guessing the change in A1 comes first? I’m very new to macros so apologies - I hope the above is clear?

Thanks,

 

[GWteB]

I see. You're using the worksheet change event handler, which will not fire when a formula evaluates to another value. The Calculate event does.

The code below goes in the module of the worksheet to be affected.
Note there's no guard clause in case the intended new worksheet name contains invalid characters, like /?*:[]\

Private Sub Worksheet_Calculate()
    If Me.Range("A1").Value <> Me.Name Then
        Me.Name = Me.Range("A1").Value
    End If
End Sub

 

[The Great Dane]

Thank you! Works for me!

 

[GWteB]

You are welcome and thanks for letting me know