Can you reference worksheet by codename using variable?

[telesien]

Hi, I am in need of looping macro that checks value in cells and hides columns that fit the condition, which is easy.

Dim i As Integer
For i = 3 To 20

Select Case Cells(2, i)
    Case 0
        Columns(i).EntireColumn.Hidden = True

    Case ""
        Columns(i).EntireColumn.Hidden = True
                
    Case Else
        
End Select

But now I would also need to hide specific sheet as well if the conditions are true. I can't use name, because it can be anything and can't use index, because it can be moved or other sheets added. The only option left is using codename.

So is there a way I can transform

Sheet5.Visible = xlHidden

Into code that will include variable i as well?
(Sheet5 for i=3, Sheet6 for i=4,...)

PS: I can alternatively declare them objects with numbers in the name, but that seems to be just as hard

 

[jkpieterse]

You would need a function like this:

Public Function GetSheetByCodeName(sCodeName As String) As Worksheet
    Dim oSh As Worksheet
    For Each oSh In ThisWorkbook.Worksheets
        If lCase(oSh.CodeName) = lCase(sCodeName) Then
            Set GetSheetByCodeName = oSh
            Exit Function
        End If
    Next
End Function

So to hide a sheet with codename Sheet5:

GetSheetByCodeName("Sheet5").Visisble = xlSHeetHidden

 

[steve the fish]

What do you mean if the condition is true? You are doing 18 tests per sheet. However, that said, the way you have that set up you can just use ActiveSheet.

 

[telesien]

You would need a function like this:

Public Function GetSheetByCodeName(sCodeName As String) As Worksheet
    Dim oSh As Worksheet
    For Each oSh In ThisWorkbook.Worksheets
        If lCase(oSh.CodeName) = lCase(sCodeName) Then
            Set GetSheetByCodeName = oSh
            Exit Function
        End If
    Next
End Function

So to hide a sheet with codename Sheet5:

GetSheetByCodeName("Sheet5").Visisble = xlSHeetHidden

Thanks, but that's a bit too complicated for me. If there is no simple option, I will have to give up on looping and just write it the old fashioned way one by one. Luckily it isn't that much cells I need to check

 

[jkpieterse]

You could circumvent the problem by using a formula that points to a cell on the sheet in question and read that formula from VBA. If the sheet gets renamed, the formula will automatically change too. You then get the right sheetname like so:

Dim sSheetName As String
sSheetName = Replace(Mid(ActiveCell.Formula, 2, InstrRev(ActiveCell.Formula, "!"), "'", "")

After which you can simply:

ThisWorkbook.Worksheets(sSheetname).Visible = xlSheetHidden

 

[telesien]

Just FYI, in the end I decided to loop the part of code that hides the columns and than used if statement that hides sheets based on visibility of columns one by one. It is a bit shorter than bunch of select case statements and I feel better for having at least some loop in there

Maybe later with more practice, I will do something more sophisticated