How to use Application.DisplayInsertOptions in the xlwings API way

The DisplayInsertOptions property of the Application object in Excel is a boolean setting that controls whether Excel displays the “Insert Options” button. This button appears when you insert cells, rows, or columns into a worksheet, offering a small drop-down menu with formatting choices (like “Format Same As Left” or “Clear Formatting”) to quickly adjust the formatting of the newly inserted cells. By default, this feature is enabled in Excel. Using the xlwings API, you can programmatically check the current state of this setting or turn it on or off, which can be useful for creating a cleaner user interface during automated processes or for ensuring consistent behavior in your macros.

Syntax in xlwings:

The property is accessed through the app object, which represents the Excel application.

app.display_insert_options

This property is both readable and writable. It expects a boolean value (True or False).

• Getting the current value: current_state = app.display_insert_options
• Setting the value: app.display_insert_options = False

Parameters:
The property itself does not take parameters. When setting it, you assign a boolean value directly.

Code Examples:

1. Checking the Current Setting:
   This code checks if the Insert Options button is currently enabled and prints its status.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active

# Get the current state of DisplayInsertOptions
is_enabled = app.display_insert_options
print(f"The 'Insert Options' button is currently enabled: {is_enabled}")

# Optional: Close the connection
app.quit()

2. Disabling the Insert Options Button:
   This script turns off the display of the Insert Options button. This is often done at the start of an automation script to prevent the button from appearing and potentially interfering with the visual flow of the macro.

import xlwings as xw

app = xw.apps.active

# Disable the Insert Options button
app.display_insert_options = False
print("Insert Options button has been disabled.")

# ... Perform other tasks like inserting rows/cells ...
# Example: Insert a row at the top of the active sheet
app.books.active.sheets.active.range('1:1').insert()

# Remember to re-enable it if desired for the user later
# app.display_insert_options = True

3. Toggling the Setting Based on a Condition:
   This example shows a more practical use case where you might store the original setting, change it for your operations, and then restore it afterwards, ensuring the user’s preference is maintained.

import xlwings as xw

def run_clean_insertion():
app = xw.apps.active
original_setting = app.display_insert_options # Store original state

try:
    app.display_insert_options = False # Turn off for our operations

    # Perform a series of insertions without the button appearing
    sheet = app.books.active.sheets.active
    sheet.range('A5').insert('down') # Insert a cell, shifting down
    sheet.range('C:C').insert() # Insert a column

finally:
    app.display_insert_options = original_setting # Restore original state
    print(f"Insert Options setting restored to: {original_setting}")

if __name__ == '__main__':
    run_clean_insertion()