Sheet - Name - Index - CodeName!


Often in forums the question is asked, how a worksheet in VBA can be addressed. There are different possibilities. With the name, the index or the code name. The name is in the brackets - the code name before the brackets. In my example thus name (One) and code name (Sheet1). The index goes from top to bottom. Pay attention to it whether it in a "For Next loop" "Sheets" or "Worksheets" use, because "Sheets" means all sheets inclusive chart sheets. If you work with the name of the worksheet you have problems, if the worksheet is renamed, and if you work with the index you have problems, if the worksheet is shifted. If you work with the CodeName of the worksheet, it can be renamed and shifted. The files at the end of the article are Excelfiles of the version 2003 and 2007.


The following code belonged in "Module1"


Option Explicit
'Problem, if the worksheet is renamed.
Public Sub Name_Sheet_Name()
Dim wksSheet As Worksheet
'The code in the out-commentated line supplies an error,
'if you have chart sheets in the file.
'For Each wksSheet In ThisWorkbook.Sheets
For Each wksSheet In ThisWorkbook.Worksheets
MsgBox "Worksheet name = " & wksSheet.Name
Next
End Sub
'Problem, if the worksheet is shifted.
Public Sub Name_Sheet_Index()
Dim wksSheet As Worksheet
For Each wksSheet In ThisWorkbook.Worksheets
MsgBox "Worksheet name = " & wksSheet.Name & _
vbCrLf & " Index = " & wksSheet.Index
Next
End Sub
'If you work with the CodeName of the worksheet,
'it can be renamed and shifted.
Public Sub Name_Sheet_CodeName()
Dim wksSheet As Worksheet
For Each wksSheet In ThisWorkbook.Worksheets
MsgBox "Worksheet name = " & wksSheet.Name & _
vbCrLf & " Index = " & wksSheet.Index & _
vbCrLf & "Worksheet CodeName = " & wksSheet.CodeName
Next
End Sub
Public Sub Sample_Name()
ThisWorkbook.Worksheets("One").Range("A1").Value = "Sample Name"
End Sub
Public Sub Sample_Index()
'ThisWorkbook.Worksheets(2).Range("A1").Value = "Sample Index"
'maybe chart sheets?
ThisWorkbook.Sheets(2).Range("A1").Value = "Sample Index"
End Sub
Public Sub Sample_CodeName()
Sheet1.Range("A1").Value = "Sample CodeName"
End Sub


Sample 2003

Sample 2007

Kommentare

Beliebte Posts aus diesem Blog

Formeln - auch Array - per VBA eintragen...

Alle Dateien eines Ordners - Optional mit Unterordner

Excel -> Word in Textmarken (Bookmarks)...