CODEWITHSUNDEEP
excelvbaactivex
pranav
pranav

Reputation: 1

unable to completely protect excel worksheet

I am trying to make an access request form, where the requester can choose the access needed and submit via email for approval. In my form, I have used Text Box (ActiveX Control) for the fields where the requester can enter details manually – like their name. for some fields, like the department which they belong to, I have used Combo Box (ActiveX Control) for dropdown lists and Check Box (ActiveX Control) for choosing the access / rights they are requesting for.

Text Fields and Dropdown

check boxes

Once they have entered all the details and when they click on send button, all the fields should get locked and no more edits should be possible. I have added the below macro for protecting the sheet, and once clicked, it is showing protected, but still I am able to make changes in text boxes, checkbox and drop downs.

Dim ws As Worksheet

For Each ws In Worksheets
    ws.Protect Password:="P@ssw0rd", UserInterfaceOnly:=True
Next ws

I've tried

Sub DisableBxs() 
    TADeptCombBx1.Enabled = False 
    TADeptCombBx2.Enabled = False 
    CheckBox1.Enabled = False 
    CheckBox10.Enabled = False 
    FirstNameTxtBx.Enabled = False 
    MiddleNameTxtBx.Enabled = False 
    FirstNameTxtBx.Enabled = False 
    MiddleNameTxtBx.Enabled = False 
    FirstNameTxtBx.Enabled = False 
    MiddleNameTxtBx.Enabled = False 
End Sub

I've have got 170 text boxes, checkboxes and combo boxes all together in the sheet. When executing the code, it is giving me the error 'object not found' when debugged

Upvotes: 0

Views: 89

Answers (1)

chris neilsen
chris neilsen

Reputation: 53135

To prevent entry into the objects, you need to turn off their Enabled propertry.

To access ActiveX objects placed on a sheet you need to use the Worksheet OLEObjects collection.

Something like

Sub ProtectAndDisableControls()
    Dim ws As Worksheet
    Dim ctrl As Object
    
    ' to exclude by control Name
    For Each ws In Worksheets
        For Each ctrl In ws.OLEObjects
            If Not LCase$(ctrl.Name) Like "approve*" _
              And Not LCase$(ctrl.Name) Like "reject*" Then
                ctrl.Enabled = False
            End If
        Next ctrl
    Next ws

    ' optional,  to exclude specific control type(s)
'    For Each ws In Worksheets
'        For Each ctrl In ws.OLEObjects
'            If Not TypeOf ctrl.Object Is MSForms.CommandButton Then
'                ctrl.Enabled = False
'            End If
'        Next ctrl
'    Next ws
        
End Sub

Upvotes: 1

Related Questions