Changing Sheet Names

[Gavin Ling]

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

 

[Guest]

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

 

[Guest]

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

 

[Ron de Bruin]

You can use the change event in the Sheet module
Copy this in the sheet module and when you change B7 the sheet name will change

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
ActiveSheet.Name = Range("B7")
On Error GoTo 0
End Sub

 

[Ron de Bruin]

Oops, use this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B7"), Target) Is Nothing Then
On Error Resume Next
ActiveSheet.Name = Range("B7")
On Error GoTo 0
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl

You can use the change event in the Sheet module
Copy this in the sheet module and when you change B7 the sheet name will change

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
ActiveSheet.Name = Range("B7")
On Error GoTo 0
End Sub

 

[Gavin Ling]

Thanks to both of you, particularly to Phillip for the idiot-speak!

I don't really understand what is going on but it works. I'll pick it
up over time.

B7 was actually the result of a V-Lookup command based on the results
of a drop down box in B6. As a result your code only worked when I
clicked 'enter' on B7, which was dull. So I changed the target codes to
B6 but left the value codes as B7 and hey presto.

Feeling quite pleased with myself, which is totally unjustified given
that I didn't really solve it!

Thanks guys, I'll be back no doubt.

Gavin