Protect Worksheet with UserInterfaceOnly still stopping code.

[NickT33]

Hello,

On my sheet I have tables I want users to be able to add / remove / edit data through a form, but have them protected from manual manipulation. After running Worksheets().Protect, UserInterfaceOnly:=True on Workbook_open() it allows my code to edit and remove items, but not add.

The problem seems arise on this line, right after I add a ListRow to the table when I try to add data:

tableRowMain.Range(1, 1).Value = txtDate

I get a Run-time error (1004): Application-defined or object-defined error.

Any help is appreciated.

 

[JoeMo]

The UserInterfaceOnly := True argument must be in the code you are running. Adding it to the workbook_open event code doesn't apply it to all code in your project.

 

[NickT33]

Ok, a couple quick questions:

Do I need to put the entire line: Worksheets("").Protect Password:="", UserInterFaceOnly:=True, at the start of every sub?
Why does the code that edits the data from another form not need this to work?

I'm hella confused, why wouldn't setting the entire sheet protection to interface only on workbook launch not apply to all code manipulating that sheet?

 

[NickT33]

Nothing I am reading says I need to put the protection code anywhere but under Workbook_Open(). The other thing is that all of the code runs except when adding data to a ListRow.

 

[JoeMo]

Hard to know what's causing your issue w/o seeing the code.

 

[NickT33]

Wasn't posting it because of the length, but here it is:

Dim sort As Variant
Dim flagMuni As Boolean
Dim flagDecal As Boolean
Dim flagMat As Boolean
Dim wsI As Worksheet
Dim wsL As Worksheet


Set wsI = Worksheets("Incoming Sheet")
Set wsL = Worksheets("Lists")


'check municipality list
flagMuni = checkList("tblMunicipality", "Municipality", cmbMunicipality.Value)


'if municipality not recognized
If flagMuni = False Then


    Dim responseMuni
    
    responseMuni = MsgBox(cmbMunicipality.Value & " is not a recognized municipality, add to list?", vbYesNoCancel, "Confirmation")
    If responseMuni = vbCancel Then Exit Sub
    If responseMuni = vbYes Then
    
        'if yes
        Dim tableRowMuni As ListRow
        
        Application.ScreenUpdating = False
        Set tableRowMuni = wsL.ListObjects("tblMunicipality").ListRows.Add
        tableRowMuni.Range(1, 1).Value = cmbMunicipality
        
        'sort
        sort = sortTable("Lists", "tblMunicipality", "Municipality", 1)
        Application.ScreenUpdating = True
    End If
End If


'check decal list
flagDecal = checkList("tblDecal", "Decal of Truck", cmbDecal.Value)


'if decal not recognized
If flagDecal = False Then


    Dim responseDecal
    
    responseDecal = MsgBox(cmbDecal.Value & " is not a recognized truck decal, add to list?", vbYesNoCancel, "Confirmation")
    If responseDecal = vbCancel Then Exit Sub
    If responseDecal = vbYes Then
    
        'if yes
        Dim tableRowDecal As ListRow
        
        Application.ScreenUpdating = False
        Set tableRowDecal = wsL.ListObjects("tblDecal").ListRows.Add
        tableRowDecal.Range(1, 1).Value = cmbDecal
        
        'sort
        sort = sortTable("Lists", "tblDecal", "Decal of Truck", 1)
        Application.ScreenUpdating = True
    End If
End If


'check date
If IsDate(txtDate.Value) = False Then


    MsgBox "Check date entered."
    Exit Sub
End If


'check material entered
flagMat = checkList("tblInMaterial", "Material", cmbMaterial.Value)


'if recognized
If flagMat = True Then


    Dim count As Integer
    Dim tableRowMain As ListRow
    Dim material As String
    Dim char As String
    
    Application.ScreenUpdating = False
    
    'remove characters
    material = cmbMaterial.Value
    For count = 1 To Len(material)
        
        char = Mid(material, count, 1)
        If char = "-" Or char = "(" Or char = ")" Or char = " " Then
        
            material = Replace(material, char, "")
            count = count - 1
        End If
    Next
    
    'add to table
    Set tableRowMain = wsI.ListObjects("tbl" & material).ListRows.Add
    tableRowMain.Range(1, 1).Value = txtDate                                         <------------------error here
    tableRowMain.Range(1, 2).Value = cmbDecal
    If Left(Right(cmbState, 4), 1) = "," Then
    
        tableRowMain.Range(1, 3).Value = Right(cmbState, 2)
    Else
    
        tableRowMain.Range(1, 3).Value = cmbState
    End If
    tableRowMain.Range(1, 4).Value = cmbCounty
    tableRowMain.Range(1, 5).Value = cmbMunicipality
    tableRowMain.Range(1, 6).Value = txtYards
    tableRowMain.Range(1, 7).Value = txtTons
    
    'sort by date
    sort = sortTable("Incoming Sheet", "tbl" & material, "Date - Time", 2)
    
    'check if empty row
    If wsI.ListObjects("tbl" & material).Range.Cells(wsI.ListObjects("tbl" & material).Range.Rows.count, 1) _
    = "" Then
    
         wsI.ListObjects("tbl" & material).Range.Cells(wsI.ListObjects("tbl" & material).Range.Rows.count, 1) _
         .EntireRow.Delete
    End If
Else


    MsgBox "Material not recognized."
    Exit Sub
End If


CutCopyMode = False
Unload IncomingForm
sort = calc(wsI.OLEObjects("cmbInTotals").Object.Value, wsI.OLEObjects("cmbInType").Object.Value, _
Worksheets("Incoming Sheet"), 12)
Application.ScreenUpdating = True
wsI.ListObjects("tbl" & material).Range.Cells(1, 1).Select
ThisWorkbook.Save
MsgBox "Material successfully added."

 

[JoeMo]

What makes you think the problem is related to sheet protection? If you are convinced of that try adding a line to include UserInterfaceOnly for the sheet wsI prior to the line that's returning an error.

 

[NickT33]

Because I don't get an error when the sheet is unprotected. When I protect the sheet without including the UserInterfaceOnly line neither of these lines work:

Cells(1,1).value = txtDate 
tableRowMain.Range(1, 1).Value = txtDate

When I include UserInterfaceOnly = True only the line setting the value in the list row fails.

 

[JoeMo]

Maybe easier to simply unprotect the sheet at the start of the macro, then protect it at the end of the macro.

 

[NickT33]

I will probably wind up doing that, still frustrating I can't figure out the issue here.