none
Protect worksheet takes eternity RRS feed

  • Question

  • Is it normal that protecting a worksheet in Excel via VB.NET takes eternity? I would say it is at least five seconds per sheet (multiply that by 60 and you're close to how long it takes to protect everything in my workbook). Can I make that any faster?
    Wednesday, December 4, 2013 1:14 PM

All replies

  • Hello KilpAr,

    What code do you use for protecting/unprotecting a worksheet?

    Wednesday, December 4, 2013 1:43 PM
  • Hello KilpAr,

    What code do you use for protecting/unprotecting a worksheet?

    worksheet.Protect(UserInterfaceOnly:=True, Password:=mypassword)

    Five seconds was actually overestimating, it is more like a second. But a long while still when you repeat it 60 times.

    Also, this is with Excel 2013.

    Wednesday, December 4, 2013 2:47 PM
  • Hello KilpAr,

    Did you try to run the code against a newly created clean workbook? Do you get the same results?

    Note, the Protect method of the Worksheet class allows to specify the following parameters:

    DrawingObjects                      

    Optional

    Variant                      

    True to protect shapes. The default value is True.

    Contents                      

    Optional

    Variant                      

    True to protect contents. For a chart, this protects the entire chart. For a worksheet, this protects the locked cells. The default value is True.

    Scenarios                      

    Optional

    Variant                      

    True to protect scenarios. This argument is valid only for worksheets. The default value is True.

    Did you try to specify false instead of the default values?

    Thursday, December 5, 2013 6:29 PM