Excel 365 breaks a worksheet creating a same name class and a new worksheet with 1 at the end

%3CLINGO-SUB%20id%3D%22lingo-sub-2972297%22%20slang%3D%22en-US%22%3EExcel%20365%20breaks%20a%20worksheet%20creating%20a%20same%20name%20class%20and%20a%20new%20worksheet%20with%201%20at%20the%20end%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2972297%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20building%20an%20Excel%20workbook%20app%2C%20with%20VBA%20code.%3C%2FP%3E%3CP%3EI%20have%20a%20specific%20problem%20in%20this%20workbook%20which%20replicates%20at%20random%20with%20a%20specific%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20this%20worksheet%20codenamed%20ITEMSTATEMENT%20suddenly%20appears%20as%20a%20Class%2C%20as%20you%20can%20see%20in%20the%20screenshots%2C%20and%20the%20original%20sheet%20content%20appears%20under%20a%20slightly%20different%20codename%2C%20ITEMSTATEMENT1.%20All%20VBA%20code%20is%20retained%20in%20the%20original%20Sheet%20(now%20a%20Class)%2C%20which%20I%20can%20move%20to%20the%20newly%20created%20Sheet.%20But%20the%20VBA%20code%20does%20not%20work%20anymore%20as%20it%20refers%20to%20a%20different%20Microsoft%20Excel%20Object%20UNTIL%20I%20move%20it.%20This%20is%20unacceptable%20to%20anyone%20using%20the%20app%20as%20it%20suddenly%20loses%20functionality.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20moving%20all%20sheets%20with%20the%20affected%20one%20from%20the%20bad%20workbook%20to%20a%20new%20one%2C%20moving%20all%20sheets%20except%20the%20affected%20one%20(which%20I%20create%20from%20scratch)%20to%20a%20new%20one%2C%20changing%20its%20codename%20and%20sheet%20name%2C%20to%20no%20avail.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBeing%20a%20Class%20of%20the%20%22ThisWorkbook%22%20kind%20(its%20properties%20state%20ThisWorkbook%20as%20its%20(Name)%20property%2C%20although%20the%20original%20ThisWorkbook%20object%20is%20unaffected)%20I%20cannot%20remove%20it.%20I%20have%20tried%20all%20the%20above%20for%20at%20least%2010%20times%20and%20it%20is%20getting%20more%20frustrating%20since%20I%20am%20building%20more%20functionality%20into%20the%20Workbook%20and%20it%20takes%20more%20and%20more%20time%20to%20recover%20every%20time%20it%20happens%2C%20having%20to%20move%2Fcopy%20worksheets%2C%20code%20modules%2C%20classes%2C%20userforms%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOther%20than%20that%20the%20workbook%20is%20usable.%20Although%20I%20have%20seen%20this%20happen%20again%20and%20thus%20have%202%20classes%20and%20a%20new%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20think%20of%20something%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20Version%202110%20(Build%2014527.20276%20Click-to-Run)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22George_Thalassinos_0-1637094300337.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F327350i2E433E5E3D1E01BF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22George_Thalassinos_0-1637094300337.png%22%20alt%3D%22George_Thalassinos_0-1637094300337.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22George_Thalassinos_1-1637094352501.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F327352iBB1B9AFEA3A2E9A2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22George_Thalassinos_1-1637094352501.png%22%20alt%3D%22George_Thalassinos_1-1637094352501.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2972297%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I am building an Excel workbook app, with VBA code.

I have a specific problem in this workbook which replicates at random with a specific worksheet.

 

The problem is that this worksheet codenamed ITEMSTATEMENT suddenly appears as a Class, as you can see in the screenshots, and the original sheet content appears under a slightly different codename, ITEMSTATEMENT1. All VBA code is retained in the original Sheet (now a Class), which I can move to the newly created Sheet. But the VBA code does not work anymore as it refers to a different Microsoft Excel Object UNTIL I move it. This is unacceptable to anyone using the app as it suddenly loses functionality.

 

I have tried moving all sheets with the affected one from the bad workbook to a new one, moving all sheets except the affected one (which I create from scratch) to a new one, changing its codename and sheet name, to no avail.

 

Being a Class of the "ThisWorkbook" kind (its properties state ThisWorkbook as its (Name) property, although the original ThisWorkbook object is unaffected) I cannot remove it. I have tried all the above for at least 10 times and it is getting more frustrating since I am building more functionality into the Workbook and it takes more and more time to recover every time it happens, having to move/copy worksheets, code modules, classes, userforms etc.

 

Other than that the workbook is usable. Although I have seen this happen again and thus have 2 classes and a new worksheet.

 

Can anyone think of something? 

 

Excel Version 2110 (Build 14527.20276 Click-to-Run)

 

George_Thalassinos_0-1637094300337.png

George_Thalassinos_1-1637094352501.png

 

 

 

0 Replies