Vba PERSONAL error because Sheets Code Name

[Dao Ha Quang]

I have a personal VBA to run many excel files exported by the estimating software.
When I run it, I get error 424 because the sheet name I am using is code name.
Because the sheet name in the excel file is exported from the software, there is special characters so I can't use names.
Is there any way to fix this situation? Thank

 

[Jaafar Tribak]

You will need to fully qualify the sheets with their parent workbook.

Add a function to get the worksheet from its codename like this:

Function GetSheet(ByVal TargetWorkbookName As String, ByVal CodeName As String) As Worksheet
    Dim sh As Worksheet
    For Each sh In Workbooks(TargetWorkbookName).Sheets
        If sh.CodeName = CodeName Then
            Set GetSheet = sh
            Exit Function
        End If
    Next
End Function

Then you use the function like this :

GetSheet("Enter_The_Workbook_Name_Here", "Sheet1").Visible = False
GetSheet("Enter_The_Workbook_Name_Here", "Sheet2").Visible = False
GetSheet("Enter_The_Workbook_Name_Here", "Sheet3").Visible = False

' and so on

Bear in mind that if the workbook name contains special characters of its own, you will end up with the same problem but hopefully, this could probably be worked around if the workbook name has a specific pattern or if it is the active workbook.

 

[Alex Blakenburg]

Assumes you are working on the Active Workbook

Sub getcodename()

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Select Case ws.CodeName
            Case "Sheet1":  ws.Visible = False
            Case "Sheet3":  ws.Visible = False
        End Select
        
    Next ws

End Sub

 

[Akuini]

Are you sure the error is caused by the special/unicode characters?
When I tried it by naming a sheet with a unicode char (e.g Əsa), I was still able to access the sheet using its code name.
@Jaafar Tribak & @Alex Blakenburg, can you try it?

 

[Dao Ha Quang]

Are you sure the error is caused by the special/unicode characters?
When I tried it by naming a sheet with a unicode char (e.g Əsa), I was still able to access the sheet using its code name.
@Jaafar Tribak & @Alex Blakenburg, can you try it?

View attachment 99224

View attachment 99225

it works fine in normal excel file, But I am talking about VBA PERSONAL

 

[Alex Blakenburg]

Are you sure the error is caused by the special/unicode characters?
When I tried it by naming a sheet with a unicode char (e.g Əsa), I was still able to access the sheet using its code name.
@Jaafar Tribak & @Alex Blakenburg, can you try it?

The issue is not limited to using the Personal workbook.
The issue is that you can't use the codename to refer to a worksheet in a workbook that isn't the workbook containing the code (ThisWorkbook).

 

[Akuini]

it works fine in normal excel file, But I am talking about VBA PERSONAL

The issue is not limited to using the Personal workbook.
The issue is that you can't use the codename to refer to a worksheet in a workbook that isn't the workbook containing the code (ThisWorkbook).

Sorry, I misunderstood the issue.
Do the solutions offered by Jaafar Tribak & Alex Blakenburg work for you?

 

[RoryA]

The issue is that you can't use the codename to refer to a worksheet in a workbook that isn't the workbook containing the code (ThisWorkbook).

You can, but you have to set a reference, which would kind of defeat the purpose here, I suspect.

 

[Dao Ha Quang]

Sorry, I misunderstood the issue.
Do the solutions offered by Jaafar Tribak & Alex Blakenburg work for you?

It's not very good for my problem, I'll probably still use the simple method of copying VBA to excel files and running it.