Re : Document Not Saved

[Bernard Rey]

d33ch wrote :

I've made a schedule for my boss (Non-Profit.) For every week there's a
different sheet. We're in week 24 now and I've made sheets till week 50.

Weird thing is I cannot save the document anymore once I've opened it.

I've attached the .XLS so you can see it for yourself.

Attached workbooks are not welcome on newsgroups, as some people don't have
Broadband connection and there could be viruses inside, who knows. Anyway,
I did dare open it No success with the Mac version, but I could open it
with the Windows Excel 2003. Crashed again, but I could shortly read an
interesting error message concerning the code sheet names in your Workbook.
So I think I can explain why things are going wrong in your case.

When one has already been messing around in the VBE Editor, and then
duplicates a worksheet (control clicking on the "Week 24" sheet Tab and
creating a copy), one can see in the VBEditor that the code sheet is not
renamed by incrementing the number as one could expect, but adding a "1" to
its name: the VBA object corresponding to your new sheet is not "Sheet2
(Week 25)" but "Sheet11 (Week 25)". If you duplicate this new sheet again,
you'll have a "Sheet111 (Week 26)" VBA Object and so on. At one point, the
name is the simply too long, and the workbook is unusable...

Now, the different workarounds could be :
- Start a new workbook, paste in your template sheet and duplicate it first,
as much as required, and open the VBE only when you're done. That way the
numbering will be made the way you'd expect it.
- Always duplicate the same sheet, the first one not the latest, the
numbering will be somewhat more "normal"
- Correct the names (first line in the "Properties" windows) in the
VBEditor: in order to avoid having some "Sheet11111111111111 (Week 37)",
change it to "Sheet14 (Week 37)".

I hope you have a "lighter" but usable version of your workbook with the
macros, as the workbook could only be recovered without them...

 

[d33ch]

d33ch wrote :




Attached workbooks are not welcome on newsgroups, as some people don't have
Broadband connection and there could be viruses inside, who knows. Anyway,
I did dare open it No success with the Mac version, but I could open it
with the Windows Excel 2003. Crashed again, but I could shortly read an
interesting error message concerning the code sheet names in your Workbook.
So I think I can explain why things are going wrong in your case.

When one has already been messing around in the VBE Editor, and then
duplicates a worksheet (control clicking on the "Week 24" sheet Tab and
creating a copy), one can see in the VBEditor that the code sheet is not
renamed by incrementing the number as one could expect, but adding a "1" to
its name: the VBA object corresponding to your new sheet is not "Sheet2
(Week 25)" but "Sheet11 (Week 25)". If you duplicate this new sheet again,
you'll have a "Sheet111 (Week 26)" VBA Object and so on. At one point, the
name is the simply too long, and the workbook is unusable...

Now, the different workarounds could be :
- Start a new workbook, paste in your template sheet and duplicate it first,
as much as required, and open the VBE only when you're done. That way the
numbering will be made the way you'd expect it.
- Always duplicate the same sheet, the first one not the latest, the
numbering will be somewhat more "normal"
- Correct the names (first line in the "Properties" windows) in the
VBEditor: in order to avoid having some "Sheet11111111111111 (Week 37)",
change it to "Sheet14 (Week 37)".

I hope you have a "lighter" but usable version of your workbook with the
macros, as the workbook could only be recovered without them...

Bernard,

Your help is much appreciated!!!

I wasn't aware of the "not posting workbooks" rule.
I guess if I didn't post with attachement I still wouldn't know what was
wrong.

But I'm not in the dark anymore. Thank you!

Greetz,

d33ch