CODEWITHSUNDEEP
vbaexcel
user1283776
user1283776

Reputation: 21764

How can I set a worksheet object based on a worksheet's codename?

The worksheet name may change. Because of that I want to set the worksheet object based on the worksheet's codename. How can I do this?

My best attempt so far is:

Sub UpdateNameDropdown()

    Dim wksName As String
    wksName = ThisWorkbook.Sheets(Sheet16).Name

    Dim wks As Worksheet
    Set wks = Sheets(wksName)

End Sub

But I get a type mismatch error on the row wksName = ThisWorkbook.Sheets.Sheet16.Name

Upvotes: 2

Views: 1692

Answers (3)

user1283776
user1283776

Reputation: 21764

Thank you Gary's Student. I midified your function to the following:

Function GetWorksheetFromCodename(codeName As String) As Worksheet
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        If StrComp(wks.codeName, codeName, vbTextCompare) = 0 Then
            Set GetWorksheetFromCodename = wks
            Exit For
        End If
    Next wks
End Function

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

This uses the codename as a String

Sub CodeIt()
    Dim CodeName As String
    CodeName = "Sheet1"
    Dim WS As Worksheet, GetWorksheetFromCodeName As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        If StrComp(WS.CodeName, CodeName, vbTextCompare) = 0 Then
            Set GetWorksheetFromCodeName = WS
            Exit For
        End If
    Next WS
    MsgBox GetWorksheetFromCodeName.Name
End Sub

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149277

This?

Sub Sample()
    Dim wks As Worksheet

    Set wks = Sheet16

    With wks
        Debug.Print .Name
        '~~> Do what you want
    End With
End Sub

Upvotes: 4

Related Questions