How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4477
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

Using Excel 2003 we have discover a problem we call the "save

Customer Question

Using Excel 2003 we have discover a problem we call the "save as syndrom"
Over time when we continue to use the same file as "save as" then edit that "save as" file, the file size tends to grow. Simply doing a copy and paste of 5 cells with simple text data to a new spread sheet can result in a spread sheet that is several megs in size and can take many minutes to excute the copy and paste function.
Is there any way to delete the previous edits that are no longer visable but still remain as part of the file?
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Jess M. replied 1 year ago.
Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

Is the file extension of the Excel file .XLS or .XLSX?
Also, what is the current file size of the file?


Please let me know so that I can help you further.

Best regards,
Jess
Customer: replied 1 year ago.

When I originally asked this question it some how got flagged in the programming catagory instead of the microsoft office catagory and there seems to be no way of changing the catagory, so I am submitting it directly to you, if you would care to address it


I am having a problem in Excel 2010 deleting certain combo boxes. Most of them I have been able to delete with no problem, but I have two that I just can not find anyway to delete. If I delete the rows they are on, includes the ones above and below it, the rows are deleted and the cells are move up, but the combo box remains where it was I have enabled the developer tab, but find no way that works to delete the box

Expert:  Jess M. replied 1 year ago.

Hi Dennis,

Thank you for writing back. I believe our moderators already addressed the category problem since it is now listed under MS Office.

Regarding the deleting of combo boxes, have you tried these trick?

=> Right click on the combo box
=> There is no delete command there. Press ESC key to close the context menu but you shall see that the combo box is still selected.
=> Press the DEL key in your keyboard to delete the control (combo box)

Another way is to right click on the combo box, then click on CUT. The combobox shall be deleted.

Please give it a try and let me know your results so that I can help you further.


Best regards,
Jess

Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4477
Experience: Computer Software Specialist for more than 10 years
Jess M. and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

I have tried right click and cut - that does not work


I have tried selecting several rows in using the ones above a d below i;t


The rows do get deleted but the combo box does not get deleted


Note there were multiple copies of the section including combo boxes, selecting these rows and deleting them removed tbe combo boxes as well. But I ended up with one row with several combo boxes on it that still work.


I could delete cells between the combo boxes and the box would shift to the left as expected


I can add additional rows between the combo box and the row above it and it move down


I just can not delete it


Thank you for your reply

Expert:  Jess M. replied 1 year ago.
Dennis,

Thank you for your reply. Can you send a dummy of the file to me? Just remove the confidential data and just leave the problem combo boxes. Then upload the file to this site:

http://wikisend.com


After uploading the file, please wait for the screen to show 6-digit File ID. Post that ID when you reply so that I can download your file.

Best regards,
Jess
Customer: replied 1 year ago.

Will do this afternoon when I get back to work


Thank you

Expert:  Jess M. replied 1 year ago.
Thank you Dennis. I will wait for that dummy file so that I can check the file myself and I will just let you know then if I have it fixed and how to do it.

Regards,
Jess
Customer: replied 1 year ago.

The file I want to send I am unable to open, edit and save. It is an xls file created and edited in Excel 2003


I am attaching a much simpler file which is actually a copy and paste from the problem file prior to having my machine upgraded to Excel 2010



  • File ID: 411660

  • File size: 16.5 MB

  • Time to live: 7 days
    There is no reason the file should be this big


The following is a copy and paste from the file


 


































































































































































































VersionsPage CoutStoreStockQty/ctnCtns
A24+DF1650038cgw20083
B24+DF800038cgw20040
D24+DF1650038cgw20083
E24+DF600038cgw20030
F24+DF700038cgw20035
C20+DF500038cgw30017
G20+DF1800038cgw30060
H12200038cgw5004
J12300038cgw5006
K121025038cgw50021
L8 BS920038cgw40023
M8 BS150038cgw4004
P8 BS450038cgw40012
N4 BS50038cgw8001
R4 BS400038cgw8005
S 2 BS250060cgw10003
V 2 BS300060cgw10003
AA4 BS900038cgw80012
W4 BS770038cgw80010
X4 BS150038cgw8002
Y4 BS450038cgw8006
Z4 BS325038cgw8005
Total143400465

 


There are no complex forumulas, simple division, rounding and sums


 


I hope this helps


 


 


As for the 2nd question - not being able to delete the combo box I have attached a second file


File ID: 173854


I reduced the file greatly, leaving some combo boxes that I can delete and others that I can not detete


 


Thank you

Expert:  Jess M. replied 1 year ago.
Thank you for your reply. I got the files and I'm checking on the issue. I will let you know what I got.

Best regards,
Jess
Expert:  Jess M. replied 1 year ago.

Hi Dennis,

Thank you for patiently waiting. I got your files and here are the results of the troubleshooting that I did on your 2 Excel files.

First, the 16.5MB file. I believe it was created by an older version of Excel and it was extremely "bloated" in terms of file size. The reason for this is data corruption. That is, as your save the file over time, data stored in the file were corrupted, some of the data I should say, which was causing the file to be bloated (16.5MB now).

When I tried to open the huge file using Office 2003, the corrupted data were discarded in order for Excel to be able to open it. The same thing happened when I tried to open the huge file with my Office Excel 2010.



Now, only the readable and non-corrupted data were recovered. Here is the recovered file:

http://wikisend.com/download/406798/Week%2016%20boxes_repaired.xls

If you are using MS Excel 2010, I highly suggest that you convert the file to XLSX format so that no data will be lost when opening and editing the file with lower versions of Excel. Here is the XLSX version of the file:

http://wikisend.com/download/558828/Week 16 boxes_repaired.xlsx

Now, regarding the second file where you cannot delete some of the combo boxes, I tried deleting them with the steps that I gave you and I was able to delete them. I believe there is no problem with your Excel settings there since you were able to delete the other combo boxes. First, I was thinking if your workbook was protected or locked causing the combo boxes not to delete, but I believe the workbook is not protected since I was able to delete the controls (combo boxes).

Here is what I did to delete the combo boxes. I provide screen shots to fully describe the process.

First, you have indicated in the file that the combo boxes are not deletable, or cannot be deleted. I suggested before that you right-click on the combo box then click on CUT. Or alternatively, right click on the combo box, press ESC key to remove the context menu and leaving the combo box selected, then press the DEL key to delete the selected combo box.

Now, the combo box is indeed deleted! The only problem is, it appears NOT being deleted because there are actually several combo boxes piled up in the row that you mentioned. So you though the right-click + CUT method did not work. But it works, you just need to do it several times because there are lots ob combo boxes piled up there. There reason why they piled up is because, you might have deleted a row containing the controls. When the rows are deleted the controls are shifted to left, right, up or down piling up the combo boxes.

Here is my screen shot when I first tried to MOVE the combo boxes to see if there are still others in the row even after moving. To move the combo box, right click on the combo box and hold and then drag it, then release the mouse button. You shall see Copy Here or Move Here after releasing the mouse button. Click on Move Here. You will see then that after moving, there are still combo boxes there. These proves that the combo boxes were actually plenty but piled up on each other in the row.



After moving, I figured out that the first problem row has 4 combo boxes that are piled on top of each other. Unfortunately, in the second problem row, there are very plenty of there. Here is my screen shot after moving more than 10 of them and still there are piled up combo boxes.



Therefore, if you decide to delete them, you need to do it several times since there are actually several combo boxes overlapping or piled up on top of each other. To do this, right-click on the combo box then click on CUT and do this until all combo boxes are removed.


Please remember to rate my service positively (3-5 stars/faces) once you have all the
information you need.

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess

Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4477
Experience: Computer Software Specialist for more than 10 years
Jess M. and 4 other Microsoft Office Specialists are ready to help you
Expert:  Jess M. replied 1 year ago.
Dennis,

I found a way to select ALL of the combo boxes in your worksheet so that you can delete them ALL with just one press of the DEL key. However, if there are legitimate combo boxes in the worksheet like the rows above the two problem rows, you need to UNSELECT them before pressing the DEL key so that they will not be deleted along with the multiple duplicates that are overlapping.

Here are the steps:
  1. Open the Excel file
  2. Click on the Developer tab
  3. Under Control group, click on Design Mode and make sure it is highlighted to indicate it is turned on. Here is the screen shot:

  4. With Design Mode turned on, right click on ANY combo box. When a context menu appears, press ESC key to close the context menu but will keep the right clicked combo box still selected. Here is my screen shot of a selected combo box without the context menu:


  5. Now, press CTRL key and hold, then press the letter A to select all combo boxes. (that means Ctrl+A to select all)
  6. Here is my my screen shot:


  7. In this case, in the 2 problem rows, even the overlapping combo boxes are selected.
  8. Now press the DEL key to delete all the combo box.
  9. NOTE: If there are legitimate combo boxes that you DO NOT want to delete, to need to UNSELECT them. To do so without deselecting the problem combo boxes, please press and hold the CTRL key and click on each combo box that you want to deselect. Here is my screen shot of deselected combo boxes:


  10. With that done, you can now press the DEL key to delete all selected combo boxes.

 

Please remember to rate my service positively (3-5 stars/faces) once you have all the
information you need.

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess

Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4477
Experience: Computer Software Specialist for more than 10 years
Jess M. and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

As to the first question - the bloated file.


I guess I did not make myself clear enough, sorry about that.


The real problem file I was not able to send to you.


When I open it in Excel 2010 it takes for ever and after it is open there is not way of saving it.


I will try using a different computer when I can open it in Excel 2003 and strip out the data corporate will not let me send you.


I will let you know when I am able to post that file.


 


Regarding the send issue - combo boxes that do not delete, I am currently working my way through what you sent me, Thank you.


It is very possible that the stacking happened when I deleted the multiple sections at the bottom of the file in which case the combo boxes were probably not deleted by rather simply move up and stacked on top of one another. I think I have enough info on that now to fix it, if not I will add a reply when I send the other file to you.


Thanks again for your help

Customer: replied 1 year ago.

I have uploaded a copy of the file that I can not save using Excel 2010



  • File ID: 348884

  • File size: 5.7 M


I have also uploaded a copy of the Excel 2010 developer tab screen shot which is different than the one you sent me


Excel 2010 developer Tab.docx



  • File ID: 346254

  • File size: 120.9 KB


I have been trying to delete the combo boxes but because of the shear number of them it is taking a lot of time


Expert:  Jess M. replied 1 year ago.
Dennis,

Thank you for writing back with those information. Regarding the deleting of the duplicate combo boxes, you can bypass the Developer tab procedures. That is, you right click on ANY combo box, then a context menu appears. Press ESC to close the context menu but the combo box shall still be selected. Here is my screen shot:



When a combo box is still selected, press CTRL+A to select ALL combo boxes in the worksheet. Then press DEL to delete all combo boxes.

If you want to un-select some of the legitimate combo boxes, you need to press CTRL key while clicking on a combo box that you want to un-select. Having done that, you are free to press the DEL key to delete those selected combo boxes.

Here is my screen shot showing some combo box deselected using the CTRL+ click that I mentioned:



Regarding the bloated file, it appears corrupted or a lot or errors are contained in the said file. I will continue to look for ways to recover the data there or remove the errors if it is still possible. I will get back to you with my findings.

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4477
Experience: Computer Software Specialist for more than 10 years
Jess M. and 4 other Microsoft Office Specialists are ready to help you
Expert:  Jess M. replied 1 year ago.
Dennis,

The Excel file that is bloated, the last one that you gave me, is partially corrupted. The good thing there is that you can still open the file despite the fact that the worksheet responds too slow or draggy because of the corrupted data.

I tried to repair the file. I also discarded incompatible layout using the Document Inspector but the Print sheet is still giving the freezing problem. In fact, when you are in the Print sheet and you use the mouse button to jump between cells, the response is slow. The reason for this is because the file still contains errors that the Document Inspector is unable to remove.

Here is the repaired file. The sheets work normally except for the Print sheet. The Print sheet is actually where the freezing is coming from.

Download below:
http://wikisend.com/download/348106/Prebill Week 18 Temp_rev1.xlsx

Also, after repairing the said corrupted file, I saved it as an XLSX file so that all incompatible components will be discarded.

But a better way to completely eliminate the corrupted data from the Excel file is to extract the good parts of the file. Here are the steps:
  1. Open MS Excel 2010
  2. Click on File => click Open
  3. In the OPen dialog box, locate the corrupted file to open it. But DO NOT click on the Open button yet after selecting the corrupted file.
  4. Instead, click on the small down-arrow in the Open command button.
  5. In the menu that appears, click on Open and Repair
  6. Here is my screen shot:


  7. Now, you shall get a prompt with options to Repair or Extract Data. I already did the repair and the results are the same. The Print sheet still contains data that are corrupted or incompatible formatting causing the sheet to slow down when you are selecting cells, even causing Excel to stop responding. So please click the Extract Data button.

  8. In the next dialog box, select Recover Formulas so that all of your formulas will be intact.

  9. Then you shall get a [Repaired] workbook with all unwanted formatting stripped off. Your logo will be dropped as well so you need to add it back. However, the resulting file is error free and all corrupted data are discarded already.
  10. Save the file as XLS or XLSX for compatibility.

 

Please download the repaired file using the steps I just described above:

http://wikisend.com/download/415420/Prebill Week 18 Temp_rev2_repaired.xls

 

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated! Cool

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess

Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4477
Experience: Computer Software Specialist for more than 10 years
Jess M. and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

Thank you again for your assistance


As long as I was working in Excel 2003 there were not problems except that it was very slow. Appareltly the original file was created in Excel 97 and with all the many repeated "save as" creations. We are getting everything cleaned up and building a template to use instead of using multiple generations of "save as" files.


Thanks again and we can consider this issue closed.


 


Back to the combo box issue, should I open a new ticket and continue on with this one.


I discovered what is causing my deletion problem, just do not know how to fix it.


The problem is that the combo box it not attached to any specific cell in the spread sheet.


The result is that when you delete a series of rows containing combo boxes everything gets deleted EXCEPT for the combo boxes that simply move up to the closest undeleted row.


There is also a problem viewing the spread sheet.


If you hide a series of columns and some contain combo boxes, the combo boxes do NOT get hidden, but simply slide over to the first viewable column. When you unhide the columns the combo boxes revert to their correct position.


Is there any way to lock the combo boxes to a cell to avoid this problem?


Thank you

Expert:  Jess M. replied 1 year ago.
Dennis,

Thank you for writing back.Yes, the behavior of the combo box is exactly what you described if it is created as a Form Control. It is a floating object and you cannot control its behavior using the Format Control dialog. That is, you cannot lock it within a cell or range since it will only float. You cannot hide it when the rows or columns are hidden and you cannot delete it when rows and columns are deleted. This is because in the Format Control dialog box, "Move and size with cells" is NOT available for Form Controls. It is only available for ActiveX controls.

"Move and size with cells" setting is the one controlling how a control like a combo box behaves along with the cells. If it is an ActiveX control, you can set that option (referring to Move and size with cells) ON (just check it) and then the combo box will hide when rows or columns where it is placed is hidden. It will also be deleted when rows or columns are deleted. This is because the combo box as an ActiveX control is an "embedded" object, not a floating object.

Thus, if you need to have combo boxes that behaves in this way, you need to create the combo boxes as an ActiveX control NOT a Form control. However, you need to VBA to work with ActiveX controls unlike Form Controls where you can use the Format Dialog box to assign the data source for the control.

Here is my screen shot:


You can give it a try with a combo box from ActiveX controls. Set the option "Move and size with cells" checked and see if it is what you want as a combo box. If you find it the suited control for your Excel file, from there, you can start coding in order to make use of the ActiveX combo box.

I hope that helped.

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4477
Experience: Computer Software Specialist for more than 10 years
Jess M. and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

Thank you very much for your assistance.


Excel 2010 is sure a big learning curve from Excel 2003


Because of you help and using the built in help functions, I was able to create a "Two way Lookup"


 


Now you know why there were so many combo boxes stacked on top of each other (I deleted about 800 rows that had lots of combo boxes in them.)


 


Thanks again

Expert:  Jess M. replied 1 year ago.
You're always welcome Dennis.

Yes, Excel 2010 has indeed several features not available in Excel 2003. But in time, or as you use it, you will discover them.

If you need assistance in the future, just let me know so that I can assist you immediately!

Best regards,
Jess

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
Jess M.
Jess M.
329 Satisfied Customers
Computer Software Specialist for more than 10 years