The ProtectScenarios member of a Worksheet object in Excel’s object model is a property that returns or sets a Boolean value indicating whether scenarios on the worksheet are protected when the sheet is protected. In the context of xlwings, a Python library for interacting with Excel, this property is accessible through the api property of a sheet object, which exposes the underlying Excel VBA object model. When a worksheet is protected using the Protect method, the ProtectScenarios property can be used to control whether users are allowed to edit, delete, or modify scenarios associated with the sheet. Scenarios are part of Excel’s What-If Analysis tools, allowing users to store and compare different sets of input values. By setting ProtectScenarios to True, you can lock these scenarios to prevent unauthorized changes, which is particularly useful in shared workbooks or templates where data integrity is critical.
In xlwings, the syntax for accessing and manipulating the ProtectScenarios property is straightforward. You first obtain a reference to the desired worksheet, typically through the Book object, and then use the api property to access the Excel object model. The property can be read or written using standard Python assignment. For example, to check the current protection status for scenarios on a sheet named “Sheet1”, you can use sheet.api.ProtectScenarios. To enable or disable this protection, you assign a Boolean value to it. Note that this property only takes effect when the worksheet itself is protected via sheet.api.Protect(). The parameters for the Protect method can be specified to customize protection settings, such as allowing users to select cells or format rows, but ProtectScenarios specifically focuses on scenario protection.
Here is a code example demonstrating the use of ProtectScenarios in xlwings:
import xlwings as xw
# Connect to an existing workbook or create a new one
wb = xw.Book('example.xlsx')
sheet = wb.sheets['Sheet1']
# Check if scenarios are currently protected
current_status = sheet.api.ProtectScenarios
print(f"Scenarios protection status: {current_status}")
# Protect the worksheet with scenarios protection enabled
sheet.api.Protect(Password="mypassword", ProtectScenarios=True)
# Alternatively, set the property after protection
sheet.api.ProtectScenarios = True
# Verify the setting
print(f"After protection, scenarios protection: {sheet.api.ProtectScenarios}")
# To disable scenarios protection while keeping the sheet protected
sheet.api.Unprotect(Password="mypassword") # Unprotect first to modify settings
sheet.api.Protect(Password="mypassword", ProtectScenarios=False)
# Or set directly if already unprotected
sheet.api.ProtectScenarios = False
# Save and close
wb.save()
wb.close()
Leave a Reply