Excel tab sheet names vs Visual Basic sheet names

Question

It seems that Visual Basic can not reference sheets according to user-modified sheet names  The worksheet tabs can have their names changed  but it seems that Visual Basic still thinks of the worksheet names as Sheet1  etc   despite the workbook tab having been changed to something useful  I have this  TABname   rng Worksheet Name    Excel sheet TAB name  not VSB Sheetx name   but I would like to use sheet names in Visual Basic routines  The best I could come up so far is to Select Case the Worksheet Tab vs  Visual Basic names  which doesn t make my day  Visual Basic must know the Sheet1  Sheet2  etc   names  How can I get these associated with the Excel tab names so that I don t have to maintain a look-up table which changes with each new sheet or sheet tab re-naming

User · Answer

Perhaps I am wrong  but you can open a workbook  and select a worksheet and change its property  Name  to whatever you need it to be   This overrides the  Sheetx  naming convention   These names are also displayed in the VBA Editor   How to do this manually  1  Select the sheet in a workbook  I tend to create templates   2  Set its tab name to whatever you like   foo    3  Click on the Developer menu  which you previously enabled  right    4  Locate  Properties  and click on it  bringing up that worksheet s properties window  5  The very first item in the Alphabetic listing is  Name  and at the right of  Name  is  Sheetx   6  Click on that field and change it  how about we use  MyFav    7  Close the properties window  8  Go to the Visual Basic editor  9  Review the sheets in the workbook you just modified  10  Observe that the MicroSoft Excel Objects shows the name you just changed  MyFav   and to the right of that  in parenthesis  the worksheet tab name   foo     You can change the  CodeName programmatically if you would rather   I use non-Sheet names to facilitate my template manipulation  You are not forced to use the generic default of  Sheetx

User · Answer

This will change all worksheet objects  names  from the perspective of the VBA editor  to match that of their sheet names  from the perspective of Excel    Sub ZZ Reset Sheet CodeNames    Changes the internal object name  codename  of each sheet to it s conventional name  based on it s sheet name       Dim varItem As Variant      For Each varItem In ThisWorkbook VBProject VBComponents          Type 100 is a worksheet         If varItem Type   100 And varItem Name  lt  gt   ThisWorkbook  Then             varItem Name   varItem Properties  Name   Value         End If     Next End Sub   It is important to note that the object name  codename    Name   is being overridden by the property name  Name   and so it must be referenced as a sub-property

User · Answer

This a very basic solution  maybe I m missing the full point of the question    ActiveSheet Name will RETURN the string of the current tab name  and will reflect any future changes by the user    I just call the active sheet  set the variable and then use it as the Worksheets  object  Here I m retrieving data from a table to set up a report for a division   This macro will work on any sheet in my workbook that is formatted for the same filter  criteria and copytorange  - each division gets their own sheet and can alter the criteria and update using this single macro   Dim currRPT As String ActiveSheet Select currRPT    ActiveSheet Name  Range  A6   Select Selection RemoveSubtotal Selection AutoFilter Range  PipeData   AdvancedFilter Action  xlFilterCopy  CriteriaRange  Range         C1 D2    CopyToRange  Range  A6 L9    Unique  True Worksheets currRPT  AutoFilter Sort SortFields Clear Worksheets currRPT  AutoFilter Sort SortFields Add Key         Range  C7    SortOn  xlSortOnValues  Order  xlAscending  DataOption         xlSortNormal

User · Answer

Actually  Sheet1  object   code name can be changed  In VBA  click on Sheet1 in Excel Objects list  In the properties window  you can change Sheet1 to say rng   Then you can reference rng as a global object without having to create a variable first  So debug print rng name works just fine  No more Worksheets  rng   name   Unlike the tab  the object name has same restrictions as other variables  i e  no spaces

User · Answer

I have had to resort to this  but this has issues with upkeep   Function sheet match rng As Range  As String    Converts Excel TAB names to the required VSB Sheetx names    TABname   rng Worksheet Name                  Excel sheet TAB name  not VSB Sheetx name  Thanks  Bill Gates    Next  match this Excel sheet TAB name to the VSB Sheetx name     Select Case TABname  sheet match       Case Is    Sheet1   sheet match    Sheet1     You supply these relationships       Case Is    Sheet2   sheet match    Sheet2        Case Is    TABnamed   sheet match    Sheet3   Re-named TAB       Case Is    Sheet4   sheet match    Sheet4        Case Is    Sheet5   sheet match    Sheet5        Case Is    Sheet6   sheet match    Sheet6        Case Is    Sheet7   sheet match    Sheet7        Case Is    Sheet8   sheet match    Sheet8     End Select End Function

User · Answer

There are  at least  two different ways to get to theWorksheet object   via the Sheets or Worksheets collections as referenced by DanM by unqualified object names   When a new workbook with three worksheets is created there will exist four objects which you can access via unqualified names  ThisWorkbook  Sheet1  Sheet2  Sheet3  This lets you write things like this   Sheet1 Range  A1   Value    foo    Although this may seem like a useful shortcut  the problem comes when the worksheets are renamed  The unqualified object name remains as Sheet1 even if the worksheet is renamed to something totally different   There is some logic to this because    worksheet names don t conform to the same rules as variable names you might accidentally mask an existing variable   For example  tested in Excel 2003   create a new Workbook with three worksheets  Create two modules  In one module declare this   Public Sheet4 As Integer   In the other module put   Sub main    Sheet4   4  MsgBox Sheet4  End Sub   Run this and the message box should appear correctly   Now add a fourth worksheet to the workbook which will create a Sheet4 object  Try running main again and this time you will get an  Object does not support this property or method  error

User · Answer

In the Excel object model a Worksheet has 2 different name properties   Worksheet Name  Worksheet CodeName  the Name property is read write and contains the name that appears on the sheet tab  It is user and VBA changeable  the CodeName property is read-only  You can reference a particular sheet as  Worksheets  Fred   Range  A1    where Fred is the  Name property or as  Sheet1 Range  A1   where Sheet1 is the codename of the worksheet

User · Answer

You should be able to reference sheets by the user-supplied name  Are you sure you re referencing the correct Workbook  If you have more than one workbook open at the time you refer to a sheet  that could definitely cause the problem   If this is the problem  using ActiveWorkbook  the currently active workbook  or ThisWorkbook  the workbook that contains the macro  should solve it   For example   Set someSheet   ActiveWorkbook Sheets  Custom Sheet

User · Answer

Using the sheet codename was the answer I needed too to stop a series of macros falling over - ccampj s answer above mirrors this solution  with screen pics

User · Answer

I think I may have an alternative solution   It s a little ugly  but it seems to work   Function GetAnyNameValue NameofName  As String     Dim nm  ws  rng As String     nm   ActiveWorkbook Names NameofName  Value     ws   CStr Split nm       0       ws   Replace ws               ws   Replace ws               rng   CStr Split nm       1       GetAnyNameValue   CStr Worksheets ws  Range rng  Value  End Function

[vba] Excel tab sheet names vs. Visual Basic sheet names

Examples related to vba

Examples related to excel

Examples related to worksheet