none
Sheet.Unprotect changes the active sheet RRS feed

  • Question

  • I have an add-in that when a protected workbook is opened (I know the password) to determine the filled cells. Since the method SpecialCells is not working if the sheet is protected I have to unprotect the sheet before calling this method. So I unprotect my workbook than for every sheet I unprotected it and call SpecialCells and after I protect it again. But after all of this even though the workbook was close with the active sheet one of the first sheets I always get the last visible sheet.

    I did some debuging and notice that after every Unprotect method the active sheet changes to the one unprotected

    Why is the method Sheet.Unprotect changes the active sheet? I tried a similar think in VBA and the active sheet was not changed.

    Are there any other solution besides keeping the active sheet and setting it back when I'm done?

    Friday, May 4, 2012 1:08 PM

All replies

  • Hi ARHANGEL19,

    Would you please tell me you Excel version, Visaul Studio version for further research?

    Best Regards,

    T.X.

    Monday, May 7, 2012 1:31 AM
  • I have Excel 2007 and Visual Studio 2010.
    Monday, May 7, 2012 4:42 AM
  • Are there any news about this?

    I tried the work around to keep the active sheet before unprotect and activate it after but when I do that than the Workbook.Deactivate event is triggered.

    Monday, May 28, 2012 12:34 PM
  • Hi Arhangel

    As this appears to be a specialized Excel question, you might try asking in the Excel for Developers forum. Someone there may have run into this before and can give you more information than you've gotten here (where the main focus is the VSTO technology).

    <<I tried the work around to keep the active sheet before unprotect and activate it after but when I do that than the Workbook.Deactivate event is triggered.>>

    This surprises me, I can't imagine why you'd get Workbook.Deactivate. When you ask this in the specialized Excel forum, be sure to include the code sections that open the workbook, do the unprotecting, and then re-activate the active sheet. Someone may see something in that combination that can help clarify what's going on.


    Cindy Meister, VSTO/Word MVP

    Monday, May 28, 2012 2:33 PM
    Moderator
  • Hi Cindy,

    I don't think it's a specialized Excel question because I tried to reproduce the same behaviour using VBA and I could not. So I still think it's an internal VSTO problem.

    I would ask on an Excel forum about my problem but since my code is in VSTO they will send me here.

    Tuesday, May 29, 2012 5:20 AM
  • Create a small Console or WinForms app and use the "Interop" to try to duplicate the behavior. If it still happens, then the problem is not related to VSTO, but to how Excel is reacting to the PIAs using these calls. Then it's definitely an Excel question and the folks there should investigate it.

    Cindy Meister, VSTO/Word MVP

    Tuesday, May 29, 2012 4:55 PM
    Moderator