Dynamic + Fixed worksheet name
 

I want to name a worksheet with a fixed name, say "Total" and a dynamic cell value which will be a number. So, if the cell reference is A1, and the value of A1 is 37, the worksheet name would be "Total 37". The value in A1 would change, hence the dynamic.

I've found how to name the worksheet just for the cell value, but I need to combine the two.

Thanks.

Dynamic + Fixed worksheet name
 

Hi Micheal,

Am Tue, 24 May 2016 12:59:44 -0700 (PDT) schrieb michaelberrier:

I want to name a worksheet with a fixed name, say "Total" and a dynamic cell value which will be a number. So, if the cell reference is A1, and the value of A1 is 37, the worksheet name would be "Total 37". The value in A1 would change, hence the dynamic.

in the code module of the expected sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "A1" Then Exit Sub
ActiveSheet.Name = "Total " & Target.Value
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Dynamic + Fixed worksheet name
 

On Tuesday, May 24, 2016 at 5:20:55 PM UTC-4, Claus Busch wrote:
Hi Micheal,

Am Tue, 24 May 2016 12:59:44 -0700 (PDT) schrieb michaelberrier:

I want to name a worksheet with a fixed name, say "Total" and a dynamic cell value which will be a number. So, if the cell reference is A1, and the value of A1 is 37, the worksheet name would be "Total 37". The value in A1 would change, hence the dynamic.

in the code module of the expected sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "A1" Then Exit Sub
ActiveSheet.Name = "Total " & Target.Value
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Ok, this will be an elementary question, but how do I get the private sub to run? I've tried doing a "Call" module, but it fails.

Dynamic + Fixed worksheet name
 

Hi Michael,

Am Wed, 25 May 2016 06:38:06 -0700 (PDT) schrieb michaelberrier:

Ok, this will be an elementary question, but how do I get the private sub to run? I've tried doing a "Call" module, but it fails.

the macro runs when you change A1


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Dynamic + Fixed worksheet name
 

On Wednesday, May 25, 2016 at 9:51:03 AM UTC-4, Claus Busch wrote:
Hi Michael,

Am Wed, 25 May 2016 06:38:06 -0700 (PDT) schrieb michaelberrier:

Ok, this will be an elementary question, but how do I get the private sub to run? I've tried doing a "Call" module, but it fails.

the macro runs when you change A1


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

It does not change.

Dynamic + Fixed worksheet name
 

On Wednesday, May 25, 2016 at 5:23:46 PM UTC-4, michaelberrier wrote:
On Wednesday, May 25, 2016 at 9:51:03 AM UTC-4, Claus Busch wrote:
Hi Michael,

Am Wed, 25 May 2016 06:38:06 -0700 (PDT) schrieb michaelberrier:

Ok, this will be an elementary question, but how do I get the private sub to run? I've tried doing a "Call" module, but it fails.

the macro runs when you change A1


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

It does not change.

Rather, the macro doesn't run when the reference cell changes

Dynamic + Fixed worksheet name
 

On Wednesday, May 25, 2016 at 5:25:48 PM UTC-4, michaelberrier wrote:
On Wednesday, May 25, 2016 at 5:23:46 PM UTC-4, michaelberrier wrote:
On Wednesday, May 25, 2016 at 9:51:03 AM UTC-4, Claus Busch wrote:
Hi Michael,

Am Wed, 25 May 2016 06:38:06 -0700 (PDT) schrieb michaelberrier:

Ok, this will be an elementary question, but how do I get the private sub to run? I've tried doing a "Call" module, but it fails.

the macro runs when you change A1


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

It does not change.

Rather, the macro doesn't run when the reference cell changes

Ok, my error. The macro runs if their is an entered value in the reference cell. I'm trying to use a formula in that cell. Is that possible?

Dynamic + Fixed worksheet name
 

Hi Michael,

Am Wed, 25 May 2016 14:28:08 -0700 (PDT) schrieb michaelberrier:

Ok, my error. The macro runs if their is an entered value in the reference cell. I'm trying to use a formula in that cell. Is that possible?

if you have a formula in A1 you have to use Worksheet_Calculate event:
Private Sub Worksheet_Calculate()
ActiveSheet.Name = "Total " & Range("A1").Value
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Dynamic + Fixed worksheet name
 

On Wednesday, May 25, 2016 at 6:08:08 PM UTC-4, Claus Busch wrote:
Hi Michael,

Am Wed, 25 May 2016 14:28:08 -0700 (PDT) schrieb michaelberrier:

Ok, my error. The macro runs if their is an entered value in the reference cell. I'm trying to use a formula in that cell. Is that possible?

if you have a formula in A1 you have to use Worksheet_Calculate event:
Private Sub Worksheet_Calculate()
ActiveSheet.Name = "Total " & Range("A1").Value
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

MONEY! Thank you sir.