Runtime Error 2147417848

Question

I have a user who gets the following error message when using a excel spreadsheet with macros that was created with excel 2003.

Runtime Error 2147417848 (80010108):

Method Protect of Object  _Worksheet failed

This user is now using excel 2010 since it is our standard. I have searched around and found similar issues that have a hot fix. Is there a hot fix for this error and if not have to do I fix the problem?

Answer 1

I have uploaded a copy of the template.

I have looked at your template, there are several issues in the code.

The issue for the error that you asked for is this code block:

  ActiveSheet.Unprotect

  ActiveWorkbook.Worksheets(1).Cells(10, 12) = _

    ActiveWorkbook.Worksheets(1).Cells(14, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(16, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(18, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(20, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(22, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(24, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(26, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(28, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(30, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(32, 23)

  ActiveSheet.Protect

When you change a cell inside the Worksheet_Change event causes that the event routine is called, means your code is called again and again and again and again ... until it crashes with the RTE.

The solution is to switch the events off before the cell is changed:

  ActiveSheet.Unprotect

  Application.EnableEvents = False

  ActiveWorkbook.Worksheets(1).Cells(10, 12) = _

    ActiveWorkbook.Worksheets(1).Cells(14, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(16, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(18, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(20, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(22, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(24, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(26, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(28, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(30, 23) + _

    ActiveWorkbook.Worksheets(1).Cells(32, 23)

  Application.EnableEvents = True

  ActiveSheet.Protect

Further more this template works only when a) Excel is newly started and b) no other file is opened and c) it can be used only once. After you worked with the template Excel must be closed completely, otherwise you get different run time errors, resp. code issues.

If you are interested in a revision, have a look into my profile for my mail address and send me a mail, I'll send you an offer.

Andreas.

Answer 2

Hi,

I have uploaded a copy of the template.

Check Request

Answer 3

That RTE can have many reasons, we need to view the file and reproduce the issue to find out what happens.

Please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

Andreas.