Name of sheet from cell?

[damian_r_Home]

Hi

Is it possible for the name of the sheet to be automatically taken from a a cell within that sheet?

If it is and its coding that does it, can you please give me exacting step by step guide on how you do it.

Hope you understand what i'm on about.


Damian

 

[Barrie Davidson]

See my second code posted and let me know if that works.

 

[damian_r]

Hi Barrie,

I've tried the second lot of code and still no luck (unless it's me!!)


Damain

 

[Barrie Davidson]

What is the error message you're receiving? Is it "Unable to change name"? If yes, does the sheet name you're trying to change to already exist?

 

[damian_r]

I'm getting the error message Unable to change name but the name does not already exist.

Any thoughts?

 

[Barrie Davidson]

Any hidden sheets in the workbook with that name? You could also take out my error routine and see what error you're getting. Change the code to:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NameRange As Range
Set NameRange = Range("A1")
If Not Intersect(Target, NameRange) Is Nothing Then
    ActiveSheet.Name = CStr(NameRange)
End If
End Sub

 

[damian_r]

Hi,

apologies for not getting back before but I've been out of the office for a couple of days.

There are no hidden sheets and when i use your latest code, everything appears to be OK.


Damian

 

[damian_r]

HELP!!!!

Having got a spreadsheet all working and running as i wanted using this sheet renaming function, i issued the revised spreadsheet to the various members of staff in the office ready for their use when returning to work today, and the sheet renaming function has stopped working on all machines except mine!!! (the spreadsheets are shared but i don't think that is the problem.

What is really strange is that it does work on my home PC as well as my work one here!!

Can somebody help with this as soon as possible.


Damian

 

[damian_r]

Problem resolved.



It appears that the macro security setting was set to high, thus causing the macros to be ignored.

Damian

 

[damian_r]

HI folks.

I've been using the above code for a while now and everything has been running fine. However i now need to use it in a slightly different manner which means that i need to sheet name to upadate to the value in the cell whenever it changes.
This change will be due to the value in the cell being taken from another sheet in the workbook
Currently this code will change the name once only and any subsequent changes in the cell are not reflected in the sheet name.

Any ideas?

Thanks

Damian

 

[damian_r]

HI again,

Sorry to bring this up again but can anybody help me on this as i really need to make this work, hence a hopefully clearer explanation of what i want below.

On sheet 1, cell A1 contains the number 1.
On sheet 2, cell c1 takes it's value from cell A1 on sheet 1.
Sheet 2 should now rename to the value in cell C1 on Sheet 2.
If the value was to change in cell 1 on sheet 1, sheet 2 should rename to the new value that appears in cell C1 on sheet 2.

Hope this is clearer.

Damian