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
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
>
>
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
>
>
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
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Gavin Ling" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
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
"Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> 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
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Gavin Ling" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks