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

 

[Aleciafxc]

Norie,

I am responding to the posts that are asking me to try something (Parry and Colin). So i do try it and am responding to them what happened.

 

[Norie]

Alecia

I'm in no way criticising you, and I apologise if you feel I am.

It just seems that you aren't giving too much away, which makes it hard to help.

Without more information we, or I am anyway, are just taking guesses/shooting in the dark.

 

[parry]

Parry,

Says my workbook is corrupt. But i do not know why? Is there any way to see why it is corrupted or how it got that way??

OK, so it couldn't fix it then. I have never seen the problem you describe before but Colin obviously has and may be able to give better advice than me.

I cannot comment on how this occurred - Colin gave you an example of how this may occur where the code has been interrupted at some point. We cannot say for sure without seeing the workbook and what code is in it that may have caused it. I fully understand why you dont want to do this since it has customer data. I guess you could make a copy of it and remove the customer data and replace names and addresses with Disney characters or something.

While painful, my opinion is your going to have to create a new workbook and start again.

You could try and see if copying a sheet to a new workbook moves the data over correctly. You can do this by right clicking the sheet tab and selecting move/copy then ensuring you tick the copy box select new workbook from the drop down. I suspect the new workbook will now be corrupt and you will have the same problem. If so your going to have to copy and paste (ctrl-A to select everything then paste into your new workbook).

Once you have copied all the sheets it will probably be linking to your old workbook. Make sure the names of your sheets correspond to the old workbook. Select Edit|links then choose the new workbook you created as the target and this will change all formulas to reference the current wkbk instead of your old one.

The code will then have to be replaced, cells reformatted and any sheet controls replaced.

regards,
Graham

 

[Colin Legg]

I've nothing much to add to Parry's spot-on reply. It does look like the current workbook is a goner. Ideally you'll have a recent back-up of the file which you can use; if not, you'll have to re-build from scratch. The good news is that is sounds like you still have access to all of the data and code (even though it doesn't run properly). If you do have build a new one, I recommend that you don't drag any existing sheets over into the new file; copy and paste the code/data/formats etc... as Parry said, so all the data is housed in fresh worksheets. If you have userforms which seem to initialise without issue, then I think it would be okay to export the userform class modules and import them into the new project rather than building their interfaces from scratch (the export/import process tends to clean up any garbage behind the scenes anyway).

Hope that helps...

 

[Aleciafxc]

Thank you all very much. I will rebuild and follow your advice.

 

[Aleciafxc]

Well, that was a learning experience. I am only responding again to say thanks to all of you the "Workbook" is very healthly now. There is no errors and is running smothly. I went back to a good backup, and rewrote much of the code and took care of a few formulas that were heavy in the NOW(). Took out all the code I was not using, but saving incase I needed it again for something esle. Read alot about what bogs workbooks up and will be changing some of our procedures. So many tweaks and changes I probabally forgot some already. With my friends here and about 35 hours cramed into two days, all data re-entered. The workbook was ready and waiting for new data entry at 8am this morning, and I am happy to report "She is a workin Smooth".

Thanks
Alecia

 

[SamTYler]

Wonderful!
























Now get back to work

 

[parry]

Well, that was a learning experience. I am only responding again to say thanks to all of you the "Workbook" is very healthly now. There is no errors and is running smothly. I went back to a good backup, and rewrote much of the code and took care of a few formulas that were heavy in the NOW(). Took out all the code I was not using, but saving incase I needed it again for something esle. Read alot about what bogs workbooks up and will be changing some of our procedures. So many tweaks and changes I probabally forgot some already. With my friends here and about 35 hours cramed into two days, all data re-entered. The workbook was ready and waiting for new data entry at 8am this morning, and I am happy to report "She is a workin Smooth".

Thanks
Alecia

That's great news. I'm only sorry we couldn't offer a "quick fix", but unfortunately when files get that bad they need to be re-created. It sounds like a blessing in disguise where you have improved out-of-sight how the file works. Long term you may want to think about separating the data from the Excel front end so if anything happens the data is always safe. Then the "front end" and data can be backed up separately.

regards,
Graham

 

[Aleciafxc]

Tell me more about that? Two files one for data and the other to analysis it???? That might give a good idea. Have done that many times but didn't think of it for this, hmmmmm. Or if that is not what you meant, then that too intrigues me.

 

[parry]

Tell me more about that? Two files one for data and the other to analysis it???? That might give a good idea. Have done that many times but didn't think of it for this, hmmmmm. Or if that is not what you meant, then that too intrigues me.

Yup thats exactly what I mean. Theres various ways to achieve this. Some places I've implemented a SQL server db that is regularly backed up with MS Access/Excel merely a way to work with the data since the business didnt want to build a front end such as web page etc. If Excel is the main method of capture (which appears to be the case for you) and you dont have SQL or a db that can store the data then another Excel file would do.

Having them separate has another benefit as well since any changes you want to make to the front end mean that you can implement very easily without having to worry about moving over the data. This means you only need one or two backups of the front end but you would have at least daily backups of the data.

Excel is really a SME type of implementation and for larger businesses I wouldn't recommend Excel as a long term place to capture/store data but it usually comes down to economics and what the business can afford.

Things you also need to consider is how many people will be using the data and their location as file sharing in itself can cause issues.

regards,
Graham