Python has several different UI (or GUI) toolkits that can be used to create forms. Which one you choose will depend on your own preferences, and all can work in Excel with PyXLL.
Here are a few options that we know have been used with PyXLL.
- PyQt5 - https://pypi.org/project/PyQt5
- tkinter - https://docs.python.org/3/library/tkinter.html
- wxWindows - https://wxpython.org
If you are already familiar with VBA UserForms and prefer to continue to use them then you can. Python functions decorated with @xl_macro can be called from VBA using the VBA "Run" method. See Calling Macros From Excel.
PyQt5 is a popular choice as it's easy to use and can be used to create complex professional looking user interfaces.
PyXLL 5 includes support for "Custom Task Panes". This allows you to embed Python windows (or widgets) into Excel seamlessly. See Custom User Interfaces in the user guide for details. As of PyXLL 5, this is the recommended way to add forms to Excel with PyXLL.
The below is relevant for versions of PyXLL prior to PyXLL 5. For new users we recommend using the Custom Task Panes feature of PyXLL create create user forms.
If you are interested in using PyQt5 with PyXLL take a look at the following blog posts:
The following shows creating a simple modal dialog using PyQt5 that is displayed to the user from an Excel menu button.
Note It may be necessary to set the QT_PLUGIN_PATH environment variable in your pyxll.cfg file. See Problem using PyQt: Could not find or load the Qt platform plugin 'windows'.
from pyxll import xl_menu, xlcAlert
from PyQt5.QtWidgets import (QApplication,
QDialog,
QVBoxLayout,
QLineEdit,
QDialogButtonBox)
import sys
def get_qt_app():
"""Returns a QApplication instance.
Must be called before showing any dialogs.
"""
app = QApplication.instance()
if app is None:
app = QApplication([sys.executable])
return app
class Form(QDialog):
def __init__(self, parent=None):
super(Form, self).__init__(parent)
self.setWindowTitle("My Form")
# Add an edit box
self.edit = QLineEdit("Enter text here..")
# Create the Ok/Cancel buttons
self.button_box = QDialogButtonBox(QDialogButtonBox.Ok
| QDialogButtonBox.Cancel)
self.button_box.clicked.connect(self.accept)
self.button_box.rejected.connect(self.reject)
# Create layout and add widgets
layout = QVBoxLayout()
layout.addWidget(self.edit)
layout.addWidget(self.button_box)
# Set dialog layout
self.setLayout(layout)
@xl_menu("Qt Dialog")
def show_qt_dialog():
# Make sure Qt has been initialized
app = get_qt_app()
# Create the custom form
dlg = Form()
dlg.exec_()
# If Ok was pressed show the text
if dlg.result():
xlcAlert(dlg.edit.text())
Calling back into Excel can be done via the Application object returned from pyxll.xl_app. See also Python as a VBA replacement.
Modal dialogs are the easiest to manage in Excel as they have their own message loop and don't interfere with Excel's existing Windows message loop. For modeless dialogs, the UI toolkit's message loop will need to be polled periodically. This should be done on the main thread, and that can be achieved using the "timer" module from the "pywin32" package.
Here is an alternative implementation of "get_qt_app" that sets up a timer to periodically poll the Qt event loop.
Note: This is only necessary if you require a modeless dialog. When creating dialogs in Excel, modal dialogs as shown above are recommended as they are much simpler and do not require the event loop to be polled.
from PyQt5.QtWidgets import QApplication
from PyQt5.QtCore import QEventLoop
import timer # requires pywin32 package
import sys
def get_qt_app():
"""Returns the global QApplication instance and starts
the event loop if necessary.
"""
app = QApplication.instance()
if app is None:
# create a new application
app = QApplication([sys.executable])
# use timer to process events periodically
processing_events = {}
def qt_timer_callback(timer_id, time):
if timer_id in processing_events:
return
processing_events[timer_id] = True
try:
app = QApplication.instance()
if app is not None:
app.processEvents(QEventLoop.AllEvents, 300)
finally:
del processing_events[timer_id]
timer.set_timer(100, qt_timer_callback)
return app
In some cases it may not be possible for all Windows events to be handled correctly by the UI toolkit when embedded inside the Excel process. For these cases, the UI can be run in a child process using the standard "subprocess" and "multiprocessing" Python packages.