[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.

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

Thanks.

[Claus Busch]

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

[michaelberrier]

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.

[Claus Busch]

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

[michaelberrier]

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.

[michaelberrier]

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

[michaelberrier]

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?

[Claus Busch]

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

[michaelberrier]

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.