Worksheet Names

[helpexcel]

Hi - I've been reading a bunch of posts on this and haven't found any that work for me. I have a workbook with a number of sheets. What i would like to happen is what ever value is put in A5 on sheet one will be used to create the worksheet names. So if A5="Red" the worksheets will be named "Red Balloons" "Red Cars" "Red Food". I plan an expanding this code for values in A10 and A20. I'm assuming the code would have to go into each worksheet individually, with a private sub?

 

[FracinDean]

Thanks for asking this, I have wanted to do it myself.

A single line in a worksheet activate sub is working for me.

Private Sub Worksheet_Activate()
ActiveSheet.Name=Range("A5").Value
End Sub

I think I remember any event called something like "AnySheetOnActivate" which might be able to do this from the workbook module, could be even better.

 

[Logit]

.
This is one method :

Option Explicit


Private Sub Worksheet_Activate()
    Me.Name = Range("A5").Value
End Sub

Paste above macro into the sheet module.

 

[helpexcel]

the ActiveSheet doesn't work, because the sheets that are changing names aren't the active sheet. Sheet1 is the ActiveSheet. I tried editing the code for the sheet module, but it didn't work.

Option Explicit
Private Sub Worksheet_Activate()


Dim ws1, LName As Worksheet


Set ws1 = ThisWorkbook.Sheets("Sheet1")
LName = ws1.Range("A5") & " " & "Balloon"


Me.Name = LName
End Sub

 

[Logit]

.

Option Explicit


Private Sub Worksheet_Activate()
    Me.Name = Range("A5").Value & " Balloon"
End Sub

 

[helpexcel]

Perfect, thanks!

How do i get that to run when the value in A5 is changed? A5 is on a different sheet than the ones being named.

 

[Logit]

.
Let's say you want to target cell F23 on Sheet3 :

Option Explicit


Private Sub Worksheet_Activate()
    Me.Name = Range("F235").Value & " Balloon"
End Sub

Place that macro in the Sheet module for Sheet 3.

 

[helpexcel]

works great, but i have to click on the sheet for the code to kick in. is there a way to make it happen with having to click on the sheet?

 

[Logit]

Will you be entering the data into the cell manually then hitting ENTER ?

Or is the cell value changing by a formula in the cell ?

Tell ya what, try this :

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    Me.Name = Range("F23").Value & " Balloon"


End Sub

 

[helpexcel]

It will be changed with a formula. So the value will be changed on Sheet1, which will update a cell on all the other sheets.