Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Sheet Names
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Sheet Names
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Sheet Names
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Sheet Names
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Sheet Names
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" wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Sheet Names
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 -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell names = sheet names | Excel Worksheet Functions | |||
Changing VB Component Names to match Worksheet names using VBE | Excel Programming | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
changing 'names' | Excel Programming | |||
changing sheet names | Excel Programming |