Desperate, Catastrophic Error

A

Aleciafxc

Board Regular
Joined
Mar 21, 2009
Messages
77
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
"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.
 
Upvote 0
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.
Click to expand...
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
 
Upvote 0
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
 
Upvote 0
Alecia

What do you mean the controls weren't active and what do you mean by 'active' controls?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Solved
Highlighting selected words in a column.
Replies
6
Views
549
Akuini
Akuini
B
  • Solved
VBA to save one file as multiple files in location
Replies
7
Views
1K
bh24524
B
R
  • Question Question
How to create an Add-in which colleagues can install giving a custom ribbon?
Replies
2
Views
2K
Fluff
Fluff
S
  • Solved
Worksheet VBA macro with splitt panes very slow
2
Replies
10
Views
1K
Peter_SSs
Peter_SSs
W
  • Question Question
Error In VBA Code
Replies
3
Views
4K
wella86
W

Forum statistics

Threads
1,223,970
Messages
6,175,699
Members
452,667
Latest member
vanessavalentino83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top