Desperate, Catastrophic Error

[Aleciafxc]

I will be specific as i can.

I know what the visual basic editor looks like with normal sheet code. Sheet1(name). It seems I have done something very wrong and can not figure it out. Now, in the visual basic editor, the Sheet Name is sheet1 with a little plain white sheet with the little excel logo this contains the code, listed as sheet1, and now the actual "data" sheet is there with what looks like the normal logo with no code. Is is like the sheet code is separated from the actual sheet. No sheet Macros works when the regular sheet is open. Some other macros work that are in the modules.

I am really in need of serious help, which brings me to another question. How can I find someone local that knows Excel Visual Basic? Do I contact Colleges, i am at a loss. The local Technical School is no help, I was the excel instructor, but not advanced macros (just good and formulas et. al.), this website is the only assistance I have had. What I have no experience in is errors and why they happen. I am in need of professional assistance which I am willing to pay for. Please Advise. Any of you Mr Excel pros in Missouri, Kansas or Oklahoma. I am Serious.

Anything will help.

Alecia

 

[SamTYler]

Give the bad workbook a password so you can open it with macros disabled.

Close it, open excel with a blank workbook, then use the File|Open menu to open the bad book.

In one of the blank workbook's code pages type
Application.VBE.ActiveVBProject
and get help on ActiveVBProject. Sorry, but that's the only way I know to open the VBE help file.

Anyway, familiarize yourself with the objects and properties and look thru the code in the bad book for those terms, especially "Designer" and "CodeModule".

Hope this helps.

PS: just in case you don't understand what i said about gettting help on ActiveVBProject, put the cursor inside the word and hit the F1 key. st

 

[SamTYler]

PPS: See http://www.mrexcel.com/forum/showthread.php?t=458989

 

[Aleciafxc]

I am taking in all the advise from the linked forums. One of my quandaries is, "Why are the sheets separated from the Sheet code?"

How did that Happen??? Any Ideas.

 

[SamTYler]

"Why are the sheets separated from the Sheet code?"

That's the VBE object model.

The three main Application objects, Excel, Forms, and VBE.

VBE.VBProjects is the collection of all open Projects. You can access a workbook's particular project with the Workbook's VBproject property.


A VBProject Modules, called VBComponents, but they don't directly hold the code. That is held by a module's CodeModule and referenced by the VBComponent's CodeModule Property.

Something that will probably work is to copy all the code in a given module to Notepad and Delete, ( "remove,") the module. Repeat for all modules in the book.

Manually PasteSpecial all worksheets Values, then Formulas, then Formats to a new book, then Insert new modules with the old names and paste the code from notepad back in them.

 

[Colin Legg]

I know what the visual basic editor looks like with normal sheet code. Sheet1(name). It seems I have done something very wrong and can not figure it out.

I'll make a guess as to what's happened.

The worksheet and its related sheet class module are intricately linked, yet distinct, objects.

When you call the worksheet.delete method, both of them are deleted (each in turn).

One way you can end up in the situation you describe is by calling worksheet.delete using an ActiveX control on the worksheet that is to be deleted. The worksheet is deleted as soon as worksheet.delete is called, but the sheet class module itself is locked and cannot be completely removed until the ActiveX control's event handler procedure ends. So, if you save the workbook after calling worksheet.delete but before the procedure ends, and then close and reopen the workbook without saving changes, you will have a 'disposed' sheet class module remaining. At this point the sheet class module is toast - procedures on it will not run, so you might as well delete it. This very situation appeared on the link below and there's an attached workbook containing a disposed sheet class module:

http://www.xtremevbtalk.com/showthread.php?t=297267

 

[Aleciafxc]

So i am not crazy. Ok, I have deleted a few worksheets in the workbook, by right clicking the sheet name and choose delete. There were active controls on the sheet but non were active at the time, at least I do not think they were. This makes alot of sense to me. All of my sheets had the code separated from them, not just one??? Can I fix this and have the code reconnect to the sheet or should I get a previous copy and rebuild from there?? What would your advise be.

Thanks
Alecia

 

[Norie]

Alecia

What do you mean the controls weren't active and what do you mean by 'active' controls?

 

[Aleciafxc]

I guess I meant that I hadn't just clicked on one of the "buttons" on the sheet i deleted. I was responding to this statement. "One way you can end up in the situation you describe is by calling worksheet.delete using an ActiveX control ". I meant to say I was not using an ActiveX control to actually do the deleting of the sheet. My terminology may not be that great.

 

[Norie]

Alecia

No problem - we can all sometimes get mixed up with terminology.

Can you explain further what this workbook does or is intended to do?

I can't see anyway that code would become 'separated' from a worksheet.