Protecting a Chart Embedded in a Worksheet with Excel VBA

If you want to protect a chart built on a worksheet, as well as the worksheet data outside a certain range, use the Protect method of the Worksheet object with the parameter UserInterfaceOnly set to True. This will protect the worksheet and allow data entry only in the specified cells. For example, to protect all worksheet objects except the range B4:G13, add the code from Listing 6.8 to the ThisWorkbook module.

To allow data entry on the worksheet, go to the Review tab on the ribbon and, in the Changes group, click Unprotect Sheet. Typically, you will be prompted to enter the password (in our case, « pass »).

Setting protection on an embedded chart. ThisWorkbook module

Private Sub Workbook_Open()
    SetPtotection
End Sub

Private Sub SetPtotection()
    On Error Resume Next
    Worksheets("Vedomost").Range("B4:G13").Locked = False
    Worksheets("Vedomost").Protect Password:="pass", UserInterfaceOnly:=True
End Sub

Note
If you add protection to a worksheet that contains controls, attempting to use the controls may cause a project error. Remove the sheet protection and perform the usual actions.

Protecting a Chart on a Separate Chart Sheet

To protect a chart located on a separate chart sheet (a Chart object), use the Protect method:

Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly)

• Password — sets the protection password.
• DrawingObjects — protects drawing objects.
• Contents — protects the entire chart.
• Scenarios — protects scenarios.
• UserInterfaceOnly — protects the user interface but not macros. If this parameter is omitted, the protection applies to both the interface and macros.

You can remove protection using Unprotect:

Unprotect(Password)

Listing shows how to protect a chart located on a separate sheet, and also how to lock all cells on worksheet Sheet1 for the user, except the range B4:G13.

Setting protection on a chart. ThisWorkbook module

Private Sub Workbook_Open()
    SetPtotection
End Sub

Private Sub SetPtotection()
    On Error Resume Next
    Charts(1).Protect Password:="d1", UserInterfaceOnly:=True
    Worksheets("Sheet1").Range("B4:G13").Locked = False
    Worksheets("Sheet1").Protect Password:="1"
End Sub