Cannot lock all columns on imported Excel sheet

Posted by: c2d3e4 on 8 September 2017, 12:52 pm EST

  • Posted 8 September 2017, 12:52 pm EST

    I imported an Excel sheet with some columns in Excel locked and sheet protected, and the other columns not locked.


    After the import, I try to lock all the columns in the spread to be read-only with codes like these:


    For Each col As FarPoint.Win.Spread.Column In FpSpread1.Sheets(0).Columns


          col.Locked = True


    Next


    FpSpread1.Sheets(0).Protect = True


    For those columns that are originally locked and sheet protected in Excel, they are now correctly locked in the spread, but the other columns are not locked and are still editable. What's wrong?  How can I lock all the columns?


    Joe


     

  • Replied 8 September 2017, 12:52 pm EST

    Joe -

    Playing around with this some I notice that setting the Locked property on the DefaultStyle for the sheet also had no effect.  If you want to work around this for the time being you can set the OperationMode to ReadOnly.
  • Replied 8 September 2017, 12:52 pm EST

    Joe -

    Your code is working for me.  Do you have a small project you could attach that reproduces the issue for us to debug?
  • Replied 8 September 2017, 12:52 pm EST

    Bob,


    Attached is a sample project.  The grayed cells in the Excel file are locked.


    Thanks!


    Joe


     


    2007/10/Testing Spread Lock.zip
  • Replied 8 September 2017, 12:52 pm EST

    Joe -

    I was able to reproduce this but I notice that the excel file has some macros in it which may be affecting the lock capability.  Do you know what these macros are doing?  Is there something different about the columns starting at the "PropName" column?  These seem to be the ones that will not lock.  Also, I noticed that when I open the xls file in excel none of the columns are locked, nor are they locked when I open the file in spread.  Any more information you can provide before I write this up as a bug would be helpful. 
  • Replied 8 September 2017, 12:52 pm EST

    Bob,


    Setting the OperationMode to ReadOnly is a workable solution for what I am doing.  Thank you!


    If you still want to know the Excel file details, I can give them to you.  I created the file.


    Joe

  • Replied 8 September 2017, 12:52 pm EST

    Joe -

    Yes, I would appreciate the details.  I'm particularly interested in what's happening with the column I mentioned earlier and those that come after it.  These seem to be the ones that are unable to be locked.
  • Replied 8 September 2017, 12:52 pm EST

    Bob,


    For the Excel file I sent you, I did not turn on the Protect Sheet, otherwise it would have been exactly what I have described. It does not seem to affect the sample project however.  You can turn the Protect Sheet on before you import it, just to be consistent with what I said earlier.


    The only macro that runs when the Excel file is in operation is the Worksheet_Change event for sheet1.  This sub converts the text dropdown to a numeric value based on the lookup tables on the Codes sheet.


    The other macros would have been executed from within another vb.net export program when the data in the Excel file are populated.  So by the time one opens up the Excel file, these macro have already been run.  They are mostly for moving data from sheet to sheet, setting up Data Validation (text or numeric dropdowns), setting cell range lock and formatting.


    Only the cells with a gray backcolor are set with lock formatting. So the PropName column, for example, is not locked.  Had I turned on the Protect Sheet, the grayed out cells will be Read-Only, while the rest will be updable.  So there is nothing special for a column like the PropName.


    Hope I answer all your questions regarding the Excel file.


    Joe


     


     

  • Replied 8 September 2017, 12:52 pm EST

    Thanks for the info, Joe.  I have written this up as a bug for the developer to fix for the next maintenance release.  The bug # is 21572.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels