How to use Application.ShowSelectionFloaties in the xlwings API way

The ShowSelectionFloaties property of the Application object in Excel is a useful feature for controlling the visibility of selection floaties—those small, dynamic pop-up toolbars that appear near a selected cell or range in Excel, offering quick access to formatting and data analysis tools like sorting, filtering, and chart recommendations. In xlwings, this property can be accessed and manipulated to enhance user experience by hiding these floaties when they might be distracting, such as during automated report generation or when running macros that require a clean interface.

Syntax and Usage in xlwings

In xlwings, you interact with Excel’s VBA object model through the app object, which represents the Excel application. The ShowSelectionFloaties property is a boolean property, meaning it can be set to either True or False. The syntax for accessing and setting this property is straightforward:

app.api.ShowSelectionFloaties

• Property Type: Boolean (bool in Python).
• Get Value: To check the current state, simply read the property: current_state = app.api.ShowSelectionFloaties. This returns True if selection floaties are visible, and False if they are hidden.
• Set Value: To change the visibility, assign a boolean value: app.api.ShowSelectionFloaties = False to hide floaties, or app.api.ShowSelectionFloaties = True to show them.

There are no parameters for this property, as it is a simple toggle. However, it’s important to note that changes made via xlwings are applied immediately to the Excel instance and affect all open workbooks. This property is part of the Excel Application object, so it controls the global setting for the entire Excel session.

Code Examples

Here are practical examples of using ShowSelectionFloaties in xlwings to manage the visibility of selection floaties:

1. Hiding Selection Floaties During an Automated Task
   This example demonstrates how to hide floaties before performing a series of operations to prevent them from interfering with the automation, then restore the original setting afterward.

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.apps.active

# Save the current state of ShowSelectionFloaties
original_state = app.api.ShowSelectionFloaties

# Hide the selection floaties
app.api.ShowSelectionFloaties = False

# Perform automated tasks, such as formatting a range
wb = app.books.active
sheet = wb.sheets[0]
sheet.range('A1:D10').value = [[i * j for j in range(1, 5)] for i in range(1, 11)]
sheet.range('A1:D10').api.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2)

# Restore the original state of ShowSelectionFloaties
app.api.ShowSelectionFloaties = original_state

2. Toggling Selection Floaties Based on User Input
   In this scenario, the script checks the current visibility and toggles it based on a condition, such as user preference from a simple input.

import xlwings as xw

app = xw.apps.active

# Simulate a user preference (e.g., from a configuration file or input)
user_wants_floaties = False # Assume user prefers hidden floaties

if user_wants_floaties:
    app.api.ShowSelectionFloaties = True
    print("Selection floaties are now visible.")
else:
    app.api.ShowSelectionFloaties = False
    print("Selection floaties are now hidden.")

3. Ensuring a Clean Interface for a Dashboard
   When generating a dashboard, you might want to hide floaties to maintain a professional appearance, especially before saving or exporting the workbook.

import xlwings as xw

app = xw.apps.active
wb = app.books.active

# Hide floaties before finalizing the dashboard
app.api.ShowSelectionFloaties = False

# Perform dashboard updates (e.g., refresh charts, pivot tables)
# ... (your dashboard code here)

# Save the workbook with floaties hidden
wb.save(r'C:\Path\To\Dashboard.xlsx')

# Optionally, re-enable floaties if needed for further interaction
# app.api.ShowSelectionFloaties = True