BenRichards
New Member
- Joined
- Apr 7, 2014
- Messages
- 41
Hi, this is my first post. I've worked with Excel/VBA for a few months, and many a times I've searched for help on something and found it here.
I use Excel 2013, and the workbook in question is saved in the 97-03 format. Five sheets as the user opens it. In it is a sheet that has checkboxes, conditional formatting, drop down menus, some VBA code in the back, the usual stuff I guess. There’s a command button with which the user can copy that sheet as a blank, without selections, into another tab, and another command button with which the user can copy the sheet as it is, with the selections. There is another sheet that collects the information from all these copied pages and presents a summary.
This workbook is giving me heartache, as it keeps crashing while saving. At one point when I tried to alter something and save, it would crash. And the save wouldn’t work. It would try to recover it, but to no success. With patience I went back into an earlier version and patiently did those updates, always freaking out when saving. Will it save or not?
At this point I started to get the feeling that the entire workbook was somehow corrupted. But I managed to do the updates I was meaning to.
The current problem is this: Usually when the user has filled in five or six sheets, then tries to save, there’s an error. "Excel cannot complete this task with available resources. Choose less data or close other applications."
This workbook isn’t that large. I wouldn’t think that the computer runs out of resources (especially as I’ve tested it while watching the task manager, and CPU resources and memory hardly budge). I have a feeling that something else is doing this.
One thing I’ve found it that the crashing isn’t related to a specific number of sheets. Sometimes I’ve filled six sheets, and then it crashes while saving. Sometimes I’ve filled 15 or 20 and I can save.
I’ve noticed the following, which could give a hint to someone: I mentioned those command buttons. I know when it’s going to crash from those command buttons. They'll freeze. If I fill that one sheet, then press the command button that copies it, and keep doing it, at some point the buttons freeze for a particular sheet. They don’t do anything when I click them. They don’t depress, they are like bricks up there. If I set it in the design mode, I can’t even select those buttons. Dead as bricks. At this point I can still activate one of those other sheets, and there the same buttons will function. I can copy that sheet, probably keep copying from there on. But at that point, it will no longer save properly.
I’ve tried to pinpoint the reason for the crashing by deleting parts of that sheet, but I haven’t been able to single out anything specific. I’ve read that conditional formatting, or pictures, or just about anything could cause a problem. I still don’t think that the workbook is too large or something like that. There’s not that much code, and I’ve worked with similar workbooks that haven’t had problems.
Any thoughts/suggestions are welcome. I’ll be happy to answer questions if you need more information. Thanks! Edit. Each time I copy that one sheet, it adds about 600 KB to the filesize, which is 3,6M to begin with.
I use Excel 2013, and the workbook in question is saved in the 97-03 format. Five sheets as the user opens it. In it is a sheet that has checkboxes, conditional formatting, drop down menus, some VBA code in the back, the usual stuff I guess. There’s a command button with which the user can copy that sheet as a blank, without selections, into another tab, and another command button with which the user can copy the sheet as it is, with the selections. There is another sheet that collects the information from all these copied pages and presents a summary.
This workbook is giving me heartache, as it keeps crashing while saving. At one point when I tried to alter something and save, it would crash. And the save wouldn’t work. It would try to recover it, but to no success. With patience I went back into an earlier version and patiently did those updates, always freaking out when saving. Will it save or not?
At this point I started to get the feeling that the entire workbook was somehow corrupted. But I managed to do the updates I was meaning to.
The current problem is this: Usually when the user has filled in five or six sheets, then tries to save, there’s an error. "Excel cannot complete this task with available resources. Choose less data or close other applications."
This workbook isn’t that large. I wouldn’t think that the computer runs out of resources (especially as I’ve tested it while watching the task manager, and CPU resources and memory hardly budge). I have a feeling that something else is doing this.
One thing I’ve found it that the crashing isn’t related to a specific number of sheets. Sometimes I’ve filled six sheets, and then it crashes while saving. Sometimes I’ve filled 15 or 20 and I can save.
I’ve noticed the following, which could give a hint to someone: I mentioned those command buttons. I know when it’s going to crash from those command buttons. They'll freeze. If I fill that one sheet, then press the command button that copies it, and keep doing it, at some point the buttons freeze for a particular sheet. They don’t do anything when I click them. They don’t depress, they are like bricks up there. If I set it in the design mode, I can’t even select those buttons. Dead as bricks. At this point I can still activate one of those other sheets, and there the same buttons will function. I can copy that sheet, probably keep copying from there on. But at that point, it will no longer save properly.
I’ve tried to pinpoint the reason for the crashing by deleting parts of that sheet, but I haven’t been able to single out anything specific. I’ve read that conditional formatting, or pictures, or just about anything could cause a problem. I still don’t think that the workbook is too large or something like that. There’s not that much code, and I’ve worked with similar workbooks that haven’t had problems.
Any thoughts/suggestions are welcome. I’ll be happy to answer questions if you need more information. Thanks! Edit. Each time I copy that one sheet, it adds about 600 KB to the filesize, which is 3,6M to begin with.
Last edited: