Macro isn't working after set .Protect UserInterfaceOnly:=True

[Vladimir_Ludovic]

Hello everyone,

This being my first post, I hope this forum can be helpfull to me, and I can to be helpfull to others. Please correct me if my style of posting is unclear or unjust.

I have a problem I am looking into for a day now, and still I can't lay my finger on it:

I have a data transfer macro, that writes cell data from a worksheet to another:

Sub cbxLocationLimits_Change()
    ' data laden vanaf Data_LocationLimits
    locrowcounter = Sheets("Data_LocationLimits").Range("A1").Value
    
    ' PRODUCT DATA
    For rowcounter = 0 To 6
        For columncounter = 0 To 1
            Sheets("InputSheet").Range("H5").Offset(rowcounter, columncounter).Value = _
            Sheets("Data_LocationLimits").Range("B3").Offset(locrowcounter - 1, 2 * rowcounter + columncounter).Value
        Next columncounter
    Next rowcounter
End Sub

When I open the document, following code is applied:

Private Sub Workbook_Open()
    Dim ws As Worksheet
     
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect UserInterfaceOnly:=True
        ws.EnableAutoFilter = True
    Next ws


    ThisWorkbook.Protect Password:=strAdminPassword, Structure:=True, Windows:=False
        

End Sub

When I use the combobox that activates the sub, Excel 2010 returns a warning message about the worksheet being read only, and doesn't resolve the transfer action.

I can't figure out why, since I used the UserInterfaceOnly:=True Flag, right? Why would excel bother about protected worksheets?

I hope someone can shine light on this!

 

[Vladimir_Ludovic]

... When I use the combobox that activates the sub ...

Problem solved: The ComboBox requires an index, linked to a cell. That cell was on the same sheet as data source (and thus, protected). Changes applied through form objects are interpreted as 'User Input' and therefore not allowed. I unlocked that one cell, and that solved the problem. Little drawback still, is that the worksheet isn't protected entirely now, but well... not a major problem.

 

[GTO]

Presuming the userform is Modal, why not unprotect the sheet(s) at the beginning of the combo box change event, and re-protect at the end?

 

[Vladimir_Ludovic]

Ah, yes. That solution is even better!

I'm not used to use callbacks of form objects yet.

Thank you!

 

[GTO]

You are most welcome and thank you for the feedback.

Mark