run macro on cell change

[dasadler]

I thought for sure this would work. It doesn't.

If cell Z68 changes, I would like to rename the worksheet tab to the value in B1. What I am doing wrong?

Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "z68" Then
ActiveSheet.Name = ActiveSheet.Range("b1")
End If
End Sub

[dasadler]

Well, I tried this (added .value) - still didn't help.

Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "z68" Then
ActiveSheet.Name = ActiveSheet.Range("b1").value
End If
End Sub

[Rudi]

Target.Address passes the address as an absolute value: "$Z$68"
This of course does not satisfy the condition: ="Z68", so the TRUE statement is always skipped.

If you set up the code like this, it will work:

Code: Select all

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$Z$68" Then
        ActiveSheet.Name = ActiveSheet.Range("b1").Value
    End If
End Sub

Alternatively, use: If ActiveSheet.Address(False,False) to pass the reference as relative: 'Z68"

[HansV]

Another way to check whether Z68 has changed:

Code: Select all

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("Z68"), Target) Is Nothing Then
        ActiveSheet.Name = ActiveSheet.Range("B1").Value
    End If
End Sub

This will even work if Z68 is changed together with other cells.

The code will fail if B1 contains a value that cannot be used as the name for a worksheet, for example if B1 is empty, or if its value contains a [, ], / or \.

[Doc.AElstein]

Don,
same again

' Similar variation again http://www.eileenslounge.com/viewtopic.php?f=27&t=24858" onclick="window.open(this.href);return false;
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("z68").Address Then Let ActiveSheet.Name = ActiveSheet.Range("b1").Value
End Sub

Alan

[dasadler]

This particular worksheet is the product of a macro (copy the page) that was run from a sheet named INV Master. When the worksheet is created from that macro it is named INV MASTER (2). My goal is to programmatically change that name.

Cell B1 contains a formula [ =LOOKUP(A3,Invoice_Start_dates,INVOICE_NUMBERS) ] that initially has a value of #N/A (because cell A3 is blank). When a date is entered in A3, then B1 looks up and displays the invoice number... something like XY16001 which I want as the name of that worksheet.

I have tried all variations offered above and none of them result in a name change.

[StuartR]

Just as an experiment can you modify Hans code above to show the name it would use in a Message Box.

Code: Select all

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("Z68"), Target) Is Nothing Then
        MsgBox "/" & ActiveSheet.Range("B1").Value & "/"
    End If
End Sub

[Doc.AElstein]

Hi Don
_.. Ah.. if a cell changes value by a Formula, then Worksheet_Change will not work
_ .. ( But I am confused now, as you are no longer talking about Cell Z68 changing )

_ .. Generally you need Worksheet_Calculate() to kick in when a cell changes its value based on it having a formula which references some other cell which you change

So something like
Private Sub Worksheet_Calculate()
Let ActiveSheet.Name = ActiveSheet.Range("b1").Value
End Sub

Alan

BTW: The code must go in the Worksheet Code Module ( Right click on the Tab and select View Code to get that )




Edit:
As Rudi said below If a cell has a formula in it, then if the Value in it changes because something that the formula references changes then the Value has changed, “updated” if you like..
The Cell has not really changed.
It is a bit of a play with words but I think it makes sense… sort of.. We usually mean ( or should mean ) an Object when we use the word “Cell”. It is a bad habit to refer to the Value in it by the word Cell. VBA encourages this bad habit by deafaulting to Cell.Value in most cases when you miss out the .Value

If I have a Power cut my light bulb goes out – the room is dark, so is the light bulb, but my light bulb has not , to a first approximation at least, changed.
_ Q : What does it talk to change a light bulb in a Prison Cell ?
_ A : Me to change it .. and a Prison Cell to have that will be Changed.

_ Q what does it take to turn the light bulb off
_ A The Prison Guard changes the switch outside the Cell and my light goes off. My Cell , as an Object , has not changed. But its Property of lightness has changed – the “Value” of the light intensity in the Cell has changed

[Rudi]

So where does Z68 fit into all of this? It's only when Z68 is actually changed that the macro will trigger. If Z68 contains a formula that updates after another cell is changed, this will still not satisfy the macro condition.

[HansV]

When a date is entered in A3, then B1 looks up and displays the invoice number... something like XY16001 which I want as the name of that worksheet.

You want the sheet name to change when you enter a value in cell A3. Hence, the code should not act when Z68 changes but when A3 changes.

Try this version. It adds error checking.

Code: Select all

Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
    If Not Intersect(Range("A3"), Target) Is Nothing And Range("B1").Value <> "" Then
        Me.Name = Range("B1").Value
    End If
    Exit Sub
ErrHandler:
    MsgBox "The name '" & Range("B1").Value & "' is not valid as a sheet name", vbCritical
End Sub

[dasadler]

Thank you all. I tried the last version from Hans and it worked fine. Sorry for the confusion all... Z68 (=B1) is what I was using for a trigger. I realize now that since that changes by formula it wouldn't work... and even if it did, it would have been more efficient to use B1 as the trigger.

I know better now - A3 is what actually gets changed manually so that is the appropriate trigger. Thank you all so much for your efforts, patience, and ability to understand what I meant despite my being wrong about basically everything I was doing.