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 Steve Your Own Question

Steve
Steve, Consultant
Category: Microsoft Office
Satisfied Customers: 275
Experience:  Steve is a consultant in the areas of computer software and programming, information management and networking.
47680681
Type Your Microsoft Office Question Here...
Steve is online now

Hopefully you can assist me with a few issues specific to

Customer Question

Hello,
Hopefully you can assist me with a few issues specific to Excel 2013. In this instance these challenges were not experienced in Excel 2007:
• I use VBA code to unprotect and protect routines to avoid key aspects of the workbook being changed such as the formulas, sheet names and the structure. In Excel 2007 this takes a fraction of a second. However, the exact same code can take up to 30 seconds in Excel 2013. I have been informed this is partially due to a stronger hashing algorithm (SHA-512) in the later versions of Excel. Therefore, I would be looking for solution or workaround to bring it closer to the Excel 2007 speed. Initially I tried adding UserInterfaceOnly = True and Application.Cursor = xlWait which was not successful on this occasion.
• In Excel 2007, I was able to use a standard macro with the unprotect and protect routines without referencing a worksheet. However, this presented a few issues in Excel 2013. This is fine in most cases, albeit there are occasions where I do not require something like Sheets("1").Select.
• The Calendar Control 12.0 and Microsoft Month View Control 6.0 (SP4) is typically located within the additional controls in the Toolbox within Excel 2007. However, this is absent in Excel 2013. I attempted to download the recommended solution on the Microsoft website. However, this was not successful.
I can attach a text file with the VBA code used to protect/unprotect if this would help.
Any help, would be most appreciated.
Rik
Submitted: 1 month ago.
Category: Microsoft Office
Expert:  Nicola-mod replied 1 month ago.
Hello,
I've been working hard to find a Professional to assist you with your question, but sometimes finding the right Professional can take a little longer than expected.
I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.
Thank you!
Nicola
Customer: replied 1 month ago.
Hi Nicola,Sure, I can wait another day. In this instance the Professional will require an understanding of some of the changes between Excel 2007 and Excel 2013, coupled with VBA.Thanks.Rik
Expert:  Nicola-mod replied 1 month ago.
Hello,
We will continue to look for a Professional to assist you.
Thank you for your patience,
Nicola
Expert:  Nicola-mod replied 1 month ago.
Hello,
I apologise as we have not yet been able to find a Professional to assist you. Do you wish for me to continue to search for someone to assist you or would you like for us to close your question at this time?
Thank you for your patience,
Nicola
Customer: replied 1 month ago.
Hi Nicola,I do not have a solution at the moment to this issue, so I will wait. It is surprising a Professional is unavailable since I was able resolve a Microsoft issue of greater complexity in the past using Just Answers.The focal challenge is the initial two points, since it is feasible I could create workaround for the third point.I have attached a relatively simple example on a text file which will unprotect the workbook, clears data then protects the workbook. The key point is the time it takes to unprotect/protect specifically in Excel 2013, rather than the earlier versions.Thanks.Rik
Expert:  Nicola-mod replied 1 month ago.
Hello,
We will continue to look for a Professional to assist you.
Thank you for your patience,
Nicola
Expert:  Jason Jones replied 1 month ago.

Hello,
I am the person that will be helping you today. Before we begin, please, understand that responding to my questions does not cost more. Also, me responding does not cost more.

May I ask for you to upload one of the documents or an example of one that you are having issues with? I have looked at your code. I don't see why it would work slow, as you stated it does. You may upload the file to this page by clicking the Send files button.

I will be standing by, looking forward to your response.
Thank you,
Jason

Customer: replied 1 month ago.
Hi Jason,No problem, please see attached a compressed example of an .xlsb file which maybe used with test data only. I have removed the password ***** open and the VBA Project coupled with the removal of the protection.If you initially open this file in Excel 2007 and test the macro buttons [Monday to Sunday and Clear] say in sheet 21 the macro runs in a fraction of a second.Furthermore, if you select the userform by pressing the macro button on Day/Weekly View on the sheet named Colleague Performance and select any range between 1 to 31, or the Monthly View button this will also run within a fraction of a second.The pop up calendar will also be evident by double clicking the cells W25 and W26 on the Colleague Performance sheet, with the benefit of Calendar Control 12.0.However, if I open in Excel 2013 using the same laptop:• The Monday to Sunday and Clear macro buttons process in 22 seconds
• If you press the macro button Monday, then shortly afterwards Clear the initial line on the Colleague Performance sheet from cell B4 is cleared even though it is not referenced on this sheet
• The Day/Weekly View and Monthly View macro buttons process in 22 seconds
• The calendar disappears since Calendar Control 12.0 is unavailable in Excel 2013Therefore, in this instance the issue seems to arise then the workbook is unprotected and then protected in the same routine in Excel 2013 which is required to ensure the data is not changed by the user.One other point in a more complex workbook in Excel 2013, these macros can take in excess of 30 seconds or even 40 seconds. This example is only 1.58 MB.Any help, would be most appreciated.Rik
Customer: replied 1 month ago.
It appears the Just Answers system does not accept .xlsb or .xlsm files and saving it in .xls 2003 would push the file over 5 MB.
Customer: replied 1 month ago.
I have removed the sheets 16 to 31 and re-saved in .xls 2003 which brings it to 3.91 MB, Therefore, the userform on the Colleague Performance sheet will only work between the range of 1 to 15.
Customer: replied 1 month ago.
This may incidentally make the macros faster since there are less sheets, than the original example which I was unable to attach.
Expert:  Jason Jones replied 1 month ago.

Thank you for the information. I am sorry, but I will not be able to help this time.

For this reason, I am opting out of the question and allowing another expert the chance to help.

The next expert will see all that is on this page, so there will be no need to repeat anything.

When another expert picks this up, you will be notified by email.

Thank you for your patience.

- Jason

Customer: replied 1 month ago.
No problem. In this instance for the benefit of the next expert, I was able to attach the original test template in the .xlsb format by attaching within a zip file.Thanks.Rik
Customer: replied 1 month ago.
Have you had the opportunity to refer this question to another expert, since Saturday?Any help, would be most appreciated.Rik
Expert:  Steve replied 1 month ago.

Hi there, my name is***** thing you can try is to apply the protection without using a password. This will avoid the SHA1 hashing but will still keep the user from messing with the sheet unless he really wants to maliciously destroy it.

Customer: replied 1 month ago.
Hi Steve,Thank-you for looking into this one. I did initially consider this one. In this instance having a password ***** workbook, with the protection may work for a one off report.However, we have had incidents in years past where workbooks which I did not personally create were unprotected by a user and key data had been changed. Therefore, at least for source data which could be used for say an invoice or something with potentially sensitive data this would present an added risk.I know some suggested UserInterfaceOnly = True would resolve the problem. However, it did not resolve the issue in my templates for some reason.Any other thoughts on this one?Thanks.Rik
Expert:  Steve replied 1 month ago.

I had a similar issue and using the protection without a password ***** the problem for me, that's why I suggested it.

It sounds like you've already tried most of these kinds of workarounds without any luck. In reality, the reason for having these complications is that you're pushing the boundaries of how a spreadsheet was designed to be used. That's not a bad thing, but it tends to reveal weaknesses in an application that don't ever get tested during development, and the intent is usually to mimic functionality that's already built in to a more suitable tool. I would say the next place to look would be at code optimization. I don't know if there are opportunities there because I haven't looked at your code. But, if you're going to rewrite code, you're probably better off putting the whole thing into a database. Once you do that, all these problems go away. Please understand that the intent of my answer here is not to be critical, but to put some "big picture" context around the issue.

Customer: replied 30 days ago.
Hi Steve,It's fine. I managed to workout a solution which brought it back down at least to 2 or 3 seconds with a bit of diligence, so I may conclude the question.In respect to the bigger picture, in some instances migration to a SQL Database has worked well, in particular where there is generic and standardised data, which is effective for managing say HR records. However, sometimes flexibility and a pure analytical approach is required due to the nature of the business, with the need for a tailored tool to resolve a problem.Thanks.Rik

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
< Previous | 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
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
  • 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
  • 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
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
  • 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
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

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

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    23
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.co.uk/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.co.uk/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    241
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.co.uk/uploads/JA/JACUSTOMERf8udkdxk/2013-8-3_15150_323738101505074393259301621172992o.64x64.jpg KamilAnwar1's Avatar

    KamilAnwar1

    Office Specialist

    Satisfied Customers:

    122
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
  • http://ww2.justanswer.co.uk/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    111
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.co.uk/uploads/WI/Windowspcfix/2013-8-19_153826_ja12.64x64.jpg James K.'s Avatar

    James K.

    Consultant

    Satisfied Customers:

    110
    Technical Director of IT Company
  • http://ww2.justanswer.co.uk/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

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

Related Microsoft Office Questions