Rename a sheet tab with values from multiple cells in the worksheet

[damian_r_Home]

Afternoon All,

I know this can be done but no idea how....

I have a worksheet and the tab name need to take the information entered into cells A1, A2, A3 & A4 and rename accordingly.
eg.
A1 = 1025
A2 = ZUT
A3 = 001
A4 = A

I'd like the tab to take these values and display them as 1025-ZUT-001-A

However, this might be the fun bit, I would like the sheet to rename ONLY WHEN there is a value entered into cell A3.

Hope there is some help with this on the horizon.

D

 

[Fluff]

How about

Sub ChangeTabName()
   If Range("A3") = "" Then Exit Sub
   ActiveSheet.Name = Join(Application.Transpose(Range("A1:A4")), "-")
End Sub

 

[JoeMo]

You could make this automatic whenever a change is made to cell A3 by using a change event macro that is installed in the sheet.
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target(1)
If Not Intersect(Target, Range("A3")) Is Nothing Then
    If Not IsEmpty(Target) Then
        Me.Name = Join(Application.Transpose(Range("A1:A4")), "-")
    End If
End If
End Sub

 

[damian_r_Home]

Afternoon Folks.

Firstly, thanks for you help with this but for some strange reason I couldn't get either method to work - it must be something i'm doing and I guess I'll look again later.

In the meantime, stay safe.

D

 

[Fluff]

In what way doesn't it work, do you get any error messages?
Or is it that it's not renaming correctly?

 

[damian_r_Home]

Hi Fluff,

It just didn't do anything.....

 

[Fluff]

Did A3 on the active sheet have a value when you ran the code?

 

[damian_r_Home]

Howdo again.

Nope, when A3 was filled in it didn't change the tab name.

D

 

[Fluff]

Who's code were you using?
If mine you need to run the macro as it isn't automatic.
If JoeMo's then have you got the code in the correct sheet module?