Missing Sheet Code Name

[indyman]

I have a very strange problem. I have a workbook with sheets 1 - 18. But there is no Sheet11 showing in Project Explorer. If I debug.print looping through all the sheets none of the code names come back wit Sheet11. However, I have msgbox that runs on Workbook>Open that is not happening at the proper timing. So I did a search for the msgbox text and I find it on Sheet14 (as I expect), but I also find it on Sheet11. Sheet14 is a User Intro. But Sheet11 does not show up. I can wipe out the code on 11, but the fact that Excel thinks there is an 11 troubles me.

To make matters more fun, a previous backup of this workbook, the User Intro is Sheet11. But that workbook is missing Sheet12. This has been a development measured in months. And I went through a few major redesigns and sheet renaming to re-order the sheets in the Project Explorer window (yep, a little OCD). So no doubt things have been moved around a bit. I have 1000's of lines of VBA, but doubt that plays a role here. I run VBA Code Cleaner often. And the sheet is not visible or hidden. one more bit of info.

I am using 13 customUI ribbon controls. I will randomly have the workbook crash close upon clicking a control (in about 1 out of 20 clicks), but never the same control, and yet to be reproducible.

Just for fun, I am going to ask the question I think I already know the answer. Is there a fix for this? Or this only explainable as a corrupt workbook? Anyone see something like this before? For now I am just going to grab a couple strong drinks and call it a day.

 

[johnnyL]

Are you referring to sheet names or code names?

Can you take a snapshot of your Alt-F11 screen and post it here so we can have a look at what you actually have going on?

 

[Michael M]

IS it possible that Sheet11 is VERY Hidden ??

 

[MARK858]

If I debug.print looping through all the sheets none of the code names come back wit Sheet11

Can you post the code that you are using to loop through the codenames please

 

[johnnyL]

IS it possible that Sheet11 is VERY Hidden ??

That is why I asked for the Alt-F11 screen pic.

 

[indyman]

Thanks for all of your input and questions. Upon doing some more testing and documenting for my reply, I have concluded the sheet indexing is all messed up, and a likely corrupt workbook, and likely created by me when I reordered the sheets in the Project Explorer. Here are some test results for fun.

Sub Test_Sh11()

    'this statement returns Subscript out of range"
    Sheets("Sheet11").Visible = xlSheetVisible '
     
    'this statement unhides Sheet13, not Sheet11
    Sheets(11).Visible = xlSheetVisible
    
    'this statement activates Sheet13, not Sheet11
    Sheets(11).Activate
    
    'this statement hides Sheet13, not sheet11
    Sheets(11).Visible = xlSheetVeryHidden

    'this statement hides Sheet15, not sheet13
    Sheets(13).Visible = xlSheetVisible
    
    'this statement hides Sheet15, not sheet13
    Sheets(13).Visible = xlSheetVeryHidden
    
    'this statement activates Sheet16, not Sheet13
    Sheets(13).Activate

    'this unhides Sheet2 as expected
    Sheets(2).Visible = xlSheetVisible

End Sub

And as I was starting to put together the screen pic some of you requested, I expanded class modules. I only use one Class and never change it so never have that expanded. But lo and behold, there were 2 more Class modules there - "Sheet7" and "Sheet11." I have no idea how they got there, but I think my own OCD to reorder the sheets is to blame. Removing those class modules still produces the same results as above. So I think I am best at this point to go through the trouble of starting with a fresh workbook and recreating all of these sheets and pasting content where appropriate. Otherwise, I can not trust this workbook for distribution. Lesson learned.

 

[Fluff]

You seem to be getting mixed up.
None of those lines of code refers to the sheet codename.
Sheets("Sheet11") is the name of the sheet as seen on the Tab.
Sheets(11) is the 11th sheet from the left (including any hidden sheets)
The sheet codename (as seen in the project explorer) is a totally different thing & has no relevance to the name of the sheet, or the order of the sheets.

 

[MARK858]

The index and the codename are 2 totally different references and so there is no reason why

   'this statement unhides Sheet13, not Sheet11
    Sheets(11).Visible = xlSheetVisible

wouldn't be correct or why the number part would match.

Edit: just seen fluff's post, indyman please read fluff's post carefully

 

[Fluff]

Further to the above 2 posts, to refer to a sheet by the code name you would use

Sheet11.Visible = xlSheetHidden

But this only works for sheets in the same workbook as the code.

 

[indyman]

@Fluff You just taught me something was not aware of. I knew that Sheets("Sheet11") refers to the tab name, but was not aware the Sheet(11) was not referencing the code name, but rather a tab order. That makes sense given you can change the code name. I thought I was saving keystrokes with the code I pasted above. In my VBA, I always set variables for sheet names, but base those off of the tab names. I might consider setting variables base on code name going forward. I see some advantages. Plus I think you just saved me a lot of rework.

Credit also to @MARK858