Hi,

As far as most of us are concerned the only dumb questions are the ones you
don't ask...and anyway, we all had to start in the same place so don't worry
about anyone thinking you're dumb...

now, you want Excel to automatically change the sheet name when the text in
cell B7 changes (that's row 7, column 2).

To do this you have to use the Worksheet events (Excel fires those
automatically in response to certain actions by the user, by code etc)

In Excel, right-click the sheet tab and click 'View Code' - that should open
the code module for the sheet you want the events to work on.

In the VB Editor, and the top of the code module, there are two drop-down
combo boxes.

The left one is for the object the event fires on, the right one is to
select the event. The left one will have something like '(General)' in it,
and in the right one you will see '(Declarations)'

On the left one, click the down-arrow, and select 'Worksheet'
On the right one, click the down arrow, and select 'Change'

VB will create a new empty event procedure for the Change event on the
worksheet. This fires EVERY time ANY cell is modified !!!

It looks like this:
CODE
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
<<< END CODE

Now, Target is the cell that has been changed, and you want the sheet name
to be changed when Cell B7 is modified.

So what you have to do is check if the cell that has changed is B7, then if
it is, you change the sheet name.

So the logic is:

Is the cell that was changed cell B7 ?
Yes: change sheet name
No: do nothing

So inside that event procedure, try code like this:

CODE
On Error GoTo err_handler

If Target.Address = "b7" Then
ActiveSheet.Name = Range("b7").Value
ElseIf Target.Column = 2 And Target.Row = 7 Then
ActiveSheet.Name = Range("b7").Value
End If

Exit Sub

err_handler:

MsgBox Err.Description
<<<END CODE

it works for me :)

If you want to debug the code step by step, then click where it says
'Private Sub', and strike the F9 kety to toggle a breakpoint (and the same to
remove it after). Then when you make a change in cell B7 and hit {enter} the
VB editor will open, and you can use F8 to step through the code ...

I hope that helps

Philip

"Gavin Ling" wrote:


Hi all,

Sorry about this, but I'm a bit of a newbie to Macros (never even
opened VB until yesterday...)

I have some code that changes a sheet name to the contents of a
particular cell:

Sub ChangeSheetNames()
ActiveSheet.Name = Range("b7")

End Sub

Now I can create a button that activates the Macro when you press it.
However I want the sheet name to change as soon as the cell contents
are changed, not when you press abutton. How do I do that?

Sorry to be dumb.

Gavin


--
Gavin Ling
------------------------------------------------------------------------
Gavin Ling's Profile: http://www.excelforum.com/member.php...o&userid=30110
View this thread: http://www.excelforum.com/showthread...hreadid=498726