Spreadsheet: 195871d1353698294-possible-to-change-sheets-names-automatically-based-on-cell-values-in-other-sheets-sheetname-from-cell-automatically
Sheet: Sheet1

Sheetname from cell, automatically
 | If you want to have your sheet name change when a cell value is changed, you can use this Worksheet_Change() event macro. Put it in your worksheet code module. Note that it has minimal error checking.
 | Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 | Const sNAMECELL As String = "A1"
 | Const sERROR As String = "Invalid worksheet name in cell "
 | Dim sSheetName As [NAME]
 | With Target
 | If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
 | sSheetName = Range(sNAMECELL).Value
 | If Not sSheetName = "" Then
 | On Error Resume Next
 | Me.Name = sSheetName
 | On Error GoTo 0
 | If Not sSheetName = Me.Name Then _
 | MsgBox sERROR & sNAMECELL
 | End If
 | End If
 | End With
 | End Sub
 | Change the value of sNAMECELL to your desired cell.
Where to put your code
XL has four types of code modules. Where you put your code has a great effect on how it will behave.
Where to find modules
Open the Visual Basic Editor (VBE) by typing OPT-F11 or choosing Tools/Macro/Visual Basic Editor... On the left side, you will see the Project Browser. Depending on which view you have set (using the Toggle Folders button), you will see either a folder hierarchy or a list of modules:
[NAME] and worksheet code modules
The ThisWorkbook and worksheet code modules are a type of class module that are tied to instances of their class objects (the [NAME] object and the worksheet objects). In the folder hierarchy, they are stored in the Microsoft Excel Objects folder. These modules generally should be reserved for event macros. As class modules, unqualified references (such as Range("A1")) refer to their class object, which is different than what happens in regular code modules (where an unqualified Range("A1") refers to the ActiveSheet at the time of evaluation). In addition, if you put your regular code into the ThisWorkbook or a sheet module, you'll have to fully qualify your macro call (e.g., Sheet1.MyMacro instead of just MyMacro). The ThisWorkbook module and Sheet modules are created automatically when you create a new workbook or insert a sheet.
Regular code modules
Regular, or Standard code modules are where you should put the majority of your macro code, your User Defined Functions (UDFs), and any global variables. In the folder hierarchy, they are stored in the [NAME] folder. These modules are accessible from everywhere in the project (unless you use Option Private), so you may put your Sub or Function in any regular code module, and you may have as many regular code modules as you wish. Unlike the workbook and worksheet code modules, unqualified references like Range("A1") will by default refer to the ActiveSheet. You can create a new regular code module in the VBE by choosing Insert/Module.
Userform code modules
Userform code modules, which are another type of class module, are tied to their Userform objects, and reside in the Forms folder within the folder hierarchy. Like worksheet code modules, these modules should be reserved for the event macros of the form (such as Initialize() and Terminate()) and its controls (such as CommandButton1_Click() or Listbox1_Change()), or code which is completely internal to the form. The Userform code module is created automatically when you create a userform in the VBE by choosing Insert/Userform.
Class modules
Class modules can be used to create new objects, with properties and methods of their own. They may inherit events from the Application object (see [NAME] treatment of Application Level Events). Class modules are created in the VBE by choosing [NAME]/Class Module, and they reside in the Class Modules folder in the folder hierarchy.
This page last updated Friday, 24 December 2004
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.