Locked Property Issue

Posted by: rainriku on 8 September 2017, 3:22 pm EST

  • Posted 8 September 2017, 3:22 pm EST

    Hi,

    I'm not familiar with the behavior of the Locked property between column,row and cell but there are cases which i set column or row locked property to true but i can still edit a certain cell. My sheet is a bit complicated,it contains unbound rows and bound columns.

    sprVw_Sheet1.Protect = True
    'Does not work
    For i As Integer = 2 To sprVw_Sheet1.Columns.Count - 1
    If sprVw_Sheet1.Columns(i).Locked = False Then
    sprVw_Sheet1.Columns(i).Locked = True
    marrListColLocked.Add(i - 2)
    End If
    Next

    'Does not work
    For i As Integer = 0 To sprVw_Sheet1.Rows.Count - 1 Step 2
    If sprVw_Sheet1.Rows(i).Locked = False Then
    sprVw_Sheet1.Rows(i).Locked = True

    End If
    Next

    'works
    sprVw_Sheet1.Cells(1,1).Locked = True

  • Replied 8 September 2017, 3:22 pm EST

    Hi,

    Locked works like the other style properties and can be set at the cell/row/column/alternating row/sheet style and inherited or overridden, depending on how it is set:
    http://sphelp.grapecity.com/WebHelp/SpreadNet9/WF/webframe.html#spwin-knowobjparentage.html

    So you can set Locked for a row or column, and all those cells will inherit the Locked setting unless they have a cell-level setting of Locked which overrides the inherited setting.

    Using DataSource to bind data to the cells in the sheet and having unbound columns and rows should not affect how the Locked property inheritance operates. If you have logic which inserts new rows and columns in the sheet for the unbound cells after binding the data, then the code might get confusing if it uses integer indexes for the columns and rows (since those indexes will shift as new rows and columns are inserted or removed in the sheet), so using Tag to set column and row names and using the overload for Columns and Rows indexer properties with string argument to search for those Tags might make that code easier to read, debug, and maintain.

    Regards,
    -Sean
  • Replied 8 September 2017, 3:22 pm EST

    Thanks for the info..

    I have now resolve the issue by setting the locked property in Cell level and not affecting the Row and Column's locked property because i will revert it back to the original after the function is used.

    For r As Integer = 0 To sprVw_Sheet1.Rows.Count - 1 Step 2
    If sprVw_Sheet1.Rows(r).Locked Then
    Continue For
    End If
    For i As Integer = 2 To sprVw_Sheet1.Columns.Count - 1
    If sprVw_Sheet1.Columns(i).Locked Then
    Continue For
    End If

    If sprVw_Sheet1.Cells(r, i).Locked = False Then
    sprVw_Sheet1.Cells(r, i).Locked = True
    marrListCellLocked.Add(r & "," & i - 2)
    End If

    Next
    Next
  • Replied 8 September 2017, 3:22 pm EST

    Hello,

    We are glad to know that your issue has been resolved.

    Thanks,
    Reeva
  • Replied 8 September 2017, 3:22 pm EST

    Thank you

    I also learned about the reset methods.

    There are already many information found on this forum
  • Replied 8 September 2017, 3:22 pm EST

    Hello,

    We are glad to know that your issue has been resolved.

    Thanks,
    Reeva
  • Replied 8 September 2017, 3:22 pm EST


    0
    down vote
    favorite
    I'm having an issue with the .locked property when I add a combobox to a sheet. I'm using the following code:

    Set cBox = Sheet1.OLEObjects.Add(ClassType:="Forms.ComboBox.1")
    With cBox
    .Left = Sheet1.Range("N" & i).Left
    .Top = Sheet1.Range("N" & i).Top
    .Width = Sheet1.Range("N" & i).Width
    .Height = Sheet1.Range("N" & i).Height
    .ListFillRange = "Sheet3!$A1:$A3"
    .Locked = False
    End With
    When I enter design mode and look at the properties of the button, it shows Locked being True still. Is there something incorrect with how I'm editing the property?

    Thanks for your time, I have 86 comboboxes, so manually unlocking them would be tedious.
  • Marked as Answer

    Replied 8 September 2017, 3:22 pm EST

    Hi,

    That code looks like VBA for Excel.
    I don't see any problem with the code, or why it isn't working to set Locked for you.
    But we can't help with Excel issues; I suggest posting your Excel VBA questions on one of these forums:
    Excel Developers
    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev
    VBA
    https://social.msdn.microsoft.com/Forums/en-us/home?forum=isvvba

    Regards,
    -Sean
Need extra support?

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

Learn More

Forum Channels