What is the Modern VBA ListBox
The ListBox is a control commonly used in VBA UserForms to create user interface-based applications. While it’s functional, the standard VBA ListBox dates back to the 1990s and lacks the advanced features found in modern web-based ListBoxes.
Enter the Modern VBA ListBox, a feature-rich upgrade designed to bridge that gap. It offers essential capabilities like filtering, sorting, pagination, and select/clear all, all while sporting a more visually appealing design than the outdated standard.
My goal in creating this was to provide a ListBox that anyone can easily add and configure, even with minimal technical expertise.
Download the Code
Download the code with a fully working demo from here
Compatibility
The Modern Listbox uses the new Worksheet Sort function, which requires either Office 365 or Excel 2021(or later).
The Video
How to Install the Modern ListBox
- Place the Modern Listbox AddIn.xlam file in a folder. The normal folder for AddIns is “C:\Users\[YourUserNameHere]\AppData\Roaming\Microsoft\AddIns”. However, you can use another folder if you prefer.
- In Excel, go to the Developer Tab->Add Ins and then Excel Add-Ins. Click Browse, select the xlam file, and click OK. The AddIn is now available to use in any VBA project.
[Optional] Convert from Xlam to an Xlsm File
If you wish, you can use the “Modern ListBox” as a normal xlsm file. To “Convert” to an xlsm file you change a setting as follows:
- Open the VB Editor.
- In the project window, click on the ThisWorkbook module of the “Modern Listbox Addin.xlam” project.
- Open the properties window if it is not already open( View->Properties or F4).
- Change the IsAddin setting to false.
- Use File->SaveAs in Excel to save the file.
How to add the Modern ListBox to a VBA Project
- To use the “Modern Listbox” in a VBA project, you add a reference to it.
- In the VBE editor, select your VBA project in the project window(Note that if you have selected the Modern Listbox in the project window then the reference will not be available).
- Select Tools and then References from the menu, and place a check by “Modern ListBox” in the list.
- The Modern Listbox is now available to use in your project.
How to use the Modern ListBox
The Modern Listbox is used with a UserForm. To create a UserForm, go to the Visual Basic Editor (press Alt + F11), right-click on the project and select Insert and UserForm. Follow the instructions below to add the Modern Listbox to a UserForm.
Add to the UserForm
Create a Frame control on your UserForm and set it to the appropriate size. The ListBox will be built within the Frame.
Note that the Search box and Page numbers will be placed above and below the ListBox, respectively, so make sure to leave space for them.
Add the Code
Declare the ListBox variable at the top of the UserForm module
Private WithEvents m_modernListbox As clsModernListbox
Add the following code to the UserForm Initialize procedure
Private Sub UserForm_Initialize() ' Create the object Set m_modernListbox = Instantiate_clsModernListbox ' Assign to the frame on the UserForm Set m_modernListbox.parentFrame = FrameListBox ' Add data to the ListBox .List = shMovies.Range("A1:F5").Value End Sub
Events
Events are procedures(i.e. subs) that are triggered when some Modern Listbox event occurs. You can use these subs to perform actions when a particular event occurs.
ItemSelected
Overview: This event is fired when an item is selected in the ListBox. Useful if you want to open another UserForm to update the current record.
Format:
Private Sub modernListBox_ItemSelected(dataRow As Long)
Code example:
Private WithEvents modernListbox As clsModernListbox Private Sub modernListBox_ItemSelected(dataRow As Long) MsgBox "The number of the row selected is: " & dataRow ' Retrieve data from the selected row Dim arr As Variant arr = m_modernListbox.GetRow(dataRow) End Sub
Note: the part of the sub name before the underline must match the variable name. In the example above it is modernListBox.
ItemDeselected
Overview: This event is fired when an item in the ListBox is double-clicked.
Format:
Private Sub modernListBox_ItemDoubleClicked(dataRow As Long)
Code example:
Private WithEvents modernListbox As clsModernListbox Private Sub modernListBox_ItemDeselected(dataRow As Long) MsgBox "The number of the row that was double-clicked is: " & dataRow ' Retrieve data from the deselected row Dim arr As Variant arr = m_modernListbox.GetRow(dataRow) End Sub
Note: the part of the sub name before the underline must match the variable name. In the example above it is modernListBox.
ItemDoubleClicked
Overview: This event is fired when an item is deselected in the ListBox.
Format:
Private Sub modernListBox_ItemDeselected(dataRow As Long)
Code example:
Private WithEvents modernListbox As clsModernListbox Private Sub modernListBox_ItemDoubleClicked(dataRow As Long) MsgBox "row number: " & dataRow & " is no longer selected." ' Retrieve data from the deselected row Dim arr As Variant arr = m_modernListbox.GetRow(dataRow) End Sub
Note: the part of the sub name before the underline must match the variable name. In the example above it is modernListBox.
Properties and Methods
This section covers the properties and methods of the ListBox
Methods are the Modern Listbox procedures and properties are the values.
AutomaticHeight
Overview: When set to true this automatically adjusts the Listbox height based on the recordsPerPage value.
Parameter:Set to True to enable automatic height adjustment; False to disable it.
Code example:
modernListbox.AutomaticHeight = True ' Turn on
modernListbox.AutomaticHeight = False ' Turn off
AutomaticWidth
Overview: When set to true this automatically adjusts the Listbox width based on the recordsPerPage value.
Parameter:Set to True to enable automatic width adjustment; False to disable it.
Code example:
modernListbox.AutomaticWidth = True ' Turn on
modernListbox.AutomaticWidth = False ' Turn off
Clear
Overview: Clears all data from the Listbox
Code example:
modernListbox.Clear
ClearHover
Overview: Used to clear the hover highlight from the listbox. This is normally used when changing focus from the Listbox to another control.
Code example:
modernListbox.ClearHover
ClearSelections
Overview: Clears the highlight from all selected rows
Code example:
modernListbox.ClearSelections
ColumnCount
Overview: returns the number of columns in the ListBox
Code example:
Dim columns As Long columns = modernListbox.ColumnCount
ColumnWidths
Overview: Sets the column widths
Parameters: Accepts a semicolon-separated string of column widths.
Code example:
modernListbox.columnWidths = "150;100;50;125;100;50"
GetRow
Overview: Retrieves the data in a given row.
Parameters
Index As Long: Index is the row number of the data that you wish to retrieve. It should not be confused with the row number on the screen, which can be different.
Return type: Variant array
Code example:
Dim rowData As Variant rowData = modernListbox.GetRow(1)
HeaderFieldsFromArray
Overview: Sets the text of the headers from a 2D array of strings. This is used to set the headers from a row in the spreadsheet.
Parameter: Accepts a 2D array of strings. Note that the array is normally a row from the worksheet.
Code example:
.HeaderFieldsFromArray = Sheet1.Range("A1:H1").Value
HeaderFieldsFromString
Overview: Sets the text of the headers from a semi-colon separated string.
Parameter: Accepts a semi-colon separated string of headers.
Code example:
.HeaderFieldsFromString = "Title;Director;Year;Genre;IMDb;Duration;Budget;Box Office"
Height
Overview: Gets or sets the height of the ListBox.
Parameter type: Long
Code example:
modernListbox.Height = 100 Height = modernListbox.Height
HoverOn
Overview: Turns on or off the Hover highlight feature
Description: When enabled, any row the mouse cursor hovers over will be highlighted
Parameter type: boolean
Code example:
modernListbox.HoverOn = True
List
Overview: Sets the data for the ListBox
Parameter type: 2D variant array, which is equivalent to a spreadsheet range
Description: When this is used, the ListBox data fields are created and the data is displayed. If you’re setting options like recordsPerPage during ListBox creation, make sure to configure them before calling List to avoid rebuilding the ListBox.
Code example:
' Do all settings before using List to prevent the ListBox being built twice modernListbox.recordsPerPage = 10 modernListbox.List = Sheet1.Range("A1:F10").Value
MultiSelect
Overview: Sets the selection type for the ListBox to either Single or Multi.
Parameter type: fmMultiSelect
Description: The options for the fmMultiSelect parameter are fmMultiSelectExtend, fmMultiSelectMulti, or fmMultiSelectSingle.
For multi-select mode, use either fmMultiSelectMulti and fmMultiSelectExtend – there is no difference between them.
For single-select mode use fmMultiSelectSingle.
Code example:
modernListbox.MultiSelect= fmMultiSelectSingle ' Single select mode modernListbox.MultiSelect= fmMultiSelectMulti ' Multi select mode
ParentFrame
Overview: Assigns the ListBox to a frame on the UserForm
Parameter type: MSForms.Frame
Description: To create the ListBox, place a Frame on the UserForm. This Frame will act as the container for the ListBox control. Use the ParentFrame property to assign the ListBox to this Frame.
Code example:
modernListbox.ParentFrame = me.Frame1
RecordsPerPage
Overview: Sets the number of records to be displayed per page
Parameter type: Long
Description: The ListBox uses pagination to display a set number of records per page. Navigation buttons at the bottom of the ListBox allow users to move between pages. The RecordsPerPage property determines how many records are shown on each page.
Code example:
modernListbox.RecordsPerPage = 10
ScrollBars
Overview: Returns or sets an Integer that specifies whether a control has vertical scroll bars, horizontal scroll bars, or both.
Parameter type: fmScrollBars
| Constant | Value | Description |
|---|---|---|
| fmScrollBarsNone | 0 | Displays no scroll bars (default). |
| fmScrollBarsHorizontal | 1 | Displays a horizontal scroll bar. |
| fmScrollBarsVertical | 2 | Displays a vertical scroll bar. |
| fmScrollBarsBoth | 3 | Displays both a horizontal and a vertical scroll bar. |
Code example:
' Displays Horizontial and Vertical Scrollbars modernListbox.ScrollBars = fmScrollBarsBoth ' No ScrollBars are displayed modernListbox.ScrollBars = fmScrollBarsNone
ScrollBarsSize
Sets the ScrollWidth size. This is the width in points that can be viewed within the control. This can only be used when the ScrollBar property is set to a value other than fmScrollBarsNone.
Code example:
' Sets the scroll bar width to 100
modernListbox.ScrollBarSize = 100
SelectAll
Overview: Selects all the items in the ListBox
Code example:
modernListbox.SelectAll
SelectedItem
Overview: Returns a variant array of the selected row with the data from all the columns in the row.
Note 1: This array can be written directly to the worksheet.
Note 2: This is a 2D array with 1 row.
Note 3: If there are multiple items selected, then this method will return the first selected item.
Code example:
' Selects record 5 Dim data As Variant data = modernListbox.SelectedItem() Sheet1.Range("A1:F1").Value = data
SelectedItems
Overview: Returns a variant array of selected rows with all the data from all the columns in those rows.
Note that this array can be written directly to the worksheet.
Code example:
Dim data As Variant
data = modernListbox.SelectedItems()
Sheet1.Range("A1:F6").Value = data
SelectedItemCount
Overview: Returns the number of items selected
Code example:
Dim itemCount As Long itemCount = modernListbox.SelectedItemCount
SetSelected
Overview: Selects a record in the ListBox, highlighting it and triggering the ItemSelected event for that record. Note that a record can be selected even if it is on a different page and not currently visible on the screen.
Setting the turnedOn to false will turn off the remove the highlight from the record.
Parameters:
Index As Long : The index is the row of the data to be selected. It should not be confused with the screen row, which may be different.
turnedOn As Boolean: Determines if the record is highlighted or not
Code example:
' Selects record 5 modernListbox.SetSelected 5, True
Width
Overview: Gets or sets the width of the ListBox.
Parameter type: Long
Code example:
modernListbox.Width = 100 Width = modernListbox.Width
Customizable Properties
The modern Excel VBA listbox includes a variety of properties that you can use to customize its appearance and behavior. While some properties are more advanced and control how the listbox functions, the ones listed below are mainly focused on visual styling. These cosmetic settings let you adjust things like fonts, colors, and layout so the listbox fits the look and feel you want.
Font Properties – Data Rows
| Property Name | Description |
|---|---|
dataFontColor |
Sets the text color for the main listbox data rows. |
dataFontName |
Sets the font family (e.g., Arial, Calibri) for data row text. |
dataFontSize |
Sets the font size for the data row text. |
️ Column Header Styling
| Property Name | Description |
|---|---|
columnHeaderBackgroundColor |
Sets the background color of the column header area. |
columnHeaderFontColor |
Sets the font color for the column headers. |
columnHeaderFontName |
Sets the font family for the column headers. |
columnHeaderFontSize |
Sets the font size for the column header text. |
️ Interaction Colors
| Property Name | Description |
|---|---|
rowSelectionColor |
Sets the background color of the currently selected row. |
hoverColorRow |
Sets the background color of a row when hovered over. |
hoverColorheader |
Sets the background color of the header when hovered over. |
Listbox Layout
| Property Name | Description |
|---|---|
listboxBackgroundColor |
Sets the background color of the entire listbox control. |
Search Box Styling
| Property Name | Description |
|---|---|
searchBoxLabelFontName |
Sets the font for the search label text (e.g., “Search:”). |
searchBoxLabelFontSize |
Sets the font size of the search label text. |
searchBoxFontName |
Sets the font family for the user-entered search text. |
searchBoxFontSize |
Sets the font size for the user-entered search text. |
What’s Next?
If you found the Modern ListBox useful, you may want to check out the Searchable Dropdown …
Thanks Paul but the link to download the code does not work. It just goes to a blank page.
Hi Gijeet,
I just tested the link and it works. Make sure that you disable any pop-up blockers.
-Paul
maybe the link will only work for those who subscribed a paid course.
The link does work and requires no payment. Simply remove any popup blockers. If you are still having issues then it may be caused by your firewall.
This is an excellent template,
However, I am facing a problem:
By selecting a row on any page, it always reference to the data in the same row on page 1.
example: Assume 10 records per page.
when selecting record 15 (= row5 on page 2) it shows data from record 5 (=row 5 on page 1)
o you have any idea, how to fix this issue? Am I missing anything?
If you use m_modernListbox.SelectedItem() it will return the first selectedItem.
You can check this in the “Demo YouTube.xlsm” workbook by clicking on “Display First” button. You will see that it displays the first selected record no matter what page is selected.
-Paul
Thank you Paul for a wonderful tool.
Are you able to make a video explaining how best to use the vba object browser ?
I’d really like to understand how to use it to see how to compose syntax – for example – is; “m_Listbox.columnHeaderFontColor = RGB(51, 153, 255)” correct ? As I get a ‘Method or data member not found’ error when I compile the code.
Hi Jim,
If you are getting an error then you are probably using an earlier version of the Modern Listbox. Download the code again to get the current version.
I haven’t any plans at the moment for a video on the Object browser.
Paul
Dear Paul, really appreciate for your wonderful listbox. I’m a very very green user in VBA. I have tried to use this listbox and it’s really great. However, I successfully got the row no. of the selected item, but I dont known how to use it to update my database record by using it.
All I want to do, is I’ve prepared another userform, once user click on the selected item, the user form will retrieve the original data in the userform and let user to update it. After submit, the updated data will be saved in the Sheet. May I seek you assistance, please.
Thanks a lot, Paul.
Hi Joffee,
You can use Selected Item to retrieve the data from the selected row.
-Paul
Great Thanks Paul, I got it. But I got another problem (really sorry for that).
As you said, the sorting function is built-in. However, I got Error 1004 and the below code is highlighted, while I clicked for sorting in the listbox. Is there any coding to activate?
m_data = WorksheetFunction.Sort(m_data, sortColumn, sortDirection)
Thanks Paul again.
You need Excel 2021(or later) or Office 365 Excel use the Worksheet Sort function. That’s most likely why you are getting this error.
I have just added a not under compatibility: “The Modern Listbox uses the new Worksheet Sort function, which requires either Office 365 or Excel 2021(or later)”
Hi Paul, thanks for your reply, unfortunately I’m using office 2019….ToT
My last last question is….
My data is to be Date in the 1st column listbox. When the listbox populate, the date format change to dd/m/yyyy, could I change the date format to yyyy/mm/dd in the listbox?
You can replace the WorksheetFunction.Sort with an array sort function which you can find online.
It’s not currently possible to format the date like that.
This is blooming awesome! Thank you Paul for sharing this. My VBA skills are not get strong enough to make good use of this, but it is inspiring to demonstrate what can be achieved.
Thanks Darren.
Hi Paul,
Just wondering if I can set a focus to the searchbox so that I can directly start typing instead of first clicking on the textbox search?
Hi Paul
I appear to be missing something somewhere. Trying to set any of the Customizable Properties (such as dataFontName, columnHeaderBackgroundColor etc) and they do not set.
I can see that when setting these the code does fire and set the relevant variable e.g m_settings.dataFontSize and this changes and remains so until you set it again (e.g. .dataFontSize = 150, then .dataFontSize = 200 <- when entering Public Property Let dataFontSize(ByVal argdataFontSize As Long) m_settings.dataFontSize is set at 150 and then subsequently changes to 200.
However nothing is actually updated in terms of what is displayed on the screen. It remains as the default font (Lato) and font size (10). The only way it actually updates is by setting/jhardcoding these in clsListBoxSettings and setting the values there instead of the default ones.
What am I missing here?
Hi Josh,
Are you using the latest version? All the issues have been resolved. You can get it here.
-Paul
Hi Paul,
Not sure if this is an issue you’ve come across before. When you have the listbox, clear it (or don’t) and use .list again you see the listbox being rebuilt on screen (even though it takes under a second to actually do
start : 06/09/2025 17:55:01
end : 06/09/2025 17:55:01
)
While you can prevent this by using windows API functions it means that the code is windows specific.
Application.ScreenUpdating = false then true don’t seem to do anything to help.
The way I found around it is below. Do you know a better way?
‘ — API DECLARATIONS —
#If VBA7 Then
‘ 64-bit compatible declarations
Private Declare PtrSafe Function LockWindowUpdate Lib “user32” (ByVal hWndLock As LongPtr) As LongPtr
Private Declare PtrSafe Function RedrawWindow Lib “user32” (ByVal hWnd As LongPtr, ByVal lprcUpdate As LongPtr, ByVal hRgnUpdate As LongPtr, ByVal fuRedraw As Long) As Long
Private Declare PtrSafe Function IUnknown_GetWindow Lib “shlwapi” Alias “#172” (ByVal pIUnknown As IUnknown, ByVal hWnd As LongPtr) As Long
#Else
‘ 32-bit declarations
Private Declare Function LockWindowUpdate Lib “user32” (ByVal hWndLock As Long) As Long
Private Declare Function RedrawWindow Lib “user32” (ByVal hWnd As Long, ByVal lprcUpdate As Long, ByVal hRgnUpdate As Long, ByVal fuRedraw As Long) As Long
Private Declare Function IUnknown_GetWindow Lib “shlwapi” Alias “#172” (ByVal pIUnknown As IUnknown, ByVal hWnd As Long) As Long
#End If
‘ — CONSTANTS —
Private Const RDW_INVALIDATE As Long = &H1
Private Const RDW_INTERNALPAINT As Long = &H2
Private Const RDW_FRAME As Long = &H400
Private Const RDW_ALLCHILDREN As Long = &H80
Private Const RDW_UPDATENOW As Long = &H100
‘ Combine flags to force full repaint including borders and scrollbars
Private Const REDRAW_ALL_FLAGS As Long = RDW_INVALIDATE Or RDW_UPDATENOW Or RDW_FRAME Or RDW_ALLCHILDREN
‘ — FUNCTION TO GET USERFORM WINDOW HANDLE —
Public Function GetUserformHwnd(ufmTarget As MSForms.UserForm) As LongPtr
IUnknown_GetWindow ufmTarget, VarPtr(GetUserformHwnd)
End Function
‘ — EXAMPLE: BUTTON CLICK TO UPDATE LISTBOX WITHOUT FLICKER —
Private Sub CommandButton1_Click()
Dim X As LongPtr
On Error GoTo CleanUp
‘ Get the window handle of the current UserForm
X = GetUserformHwnd(Me)
‘ Lock painting for this UserForm only
LockWindowUpdate X
‘ === Perform your updates here (e.g., refresh ListBox) ===
With mlb_Slots ‘ Replace with your ListBox name
.List = Sheet1.Range(“I5:J15”).Value ‘ Example data
.recordsPerPage = 16
.ScrollBars = fmScrollBarsVertical
End With
CleanUp:
‘ — Critical: Unlock window updates —
LockWindowUpdate 0
‘ — Force full repaint: client area, borders, scrollbars, children —
If X 0 Then
RedrawWindow X, 0, 0, REDRAW_ALL_FLAGS
End If
End Sub
Thanks
Josh
With fresh eyes this morning I found .SetVisible.
Using this with a second (hidden) MLB in exactly the same position gives a better experience than using either .List (in which you see all labels redraw) or locking window updates (which causes a bit of a form flash).
When needing to update the list – the following order seems best
Second MLB set .list, then make visible
set visible to false on the first list
Obviously alternate each time the list needs to be updated.
While you see the interior of the MLB go blank briefly (sometimes) before the new list is shown it’s better than watching the whole thing redraw if you just use .list and the form ‘flash’ if using the windows apis
Thanks Josh,
That’s a clever solution.
-Paul
Hi Paul
This one has me completely stumped. It’s related to the (VBA) bug you documented in clsParentFrameManager but I just can’t seem to find a way around it.
The form controls are completely dynamically built (e.g. just a blank form if you view it in designer but same issue if you build in designer as well) and is the below structure (and is built in the below order).
Form
MasterFrame1 (just for visual purposes)
MLBFrameA (child or MasterFrame1, is MLB with single selection, list from a table databodyrange etc etc etc)
MasterFrame2 (just for visual purposes)
MLBFrameB
MLBFrameC
Both MLBFrameB & C are the same frame (see the MLB repainting issue from previously) and just get switched whenever that MLB needs to update with a new list.
This all works fine without issue. The issue arises when .list needs to be used on MLBFrameA (updates to list) and is that vba frame issue (as MLBFrameA is not the last frame created….although I have no idea why MLBFrameB & C work without issue using .list given MLBFrameB is created before MLBFrameC).
I’ve tried various things including a second MLBFrameA and switching (as with B & C) but as it’s not the last frame created the bug occurs (possibly because it’s in MasterFrame1 while B&C are in MasterFrame 2). I even modified the MLB class to simply add one more row to MLBFrameA but alas the bug occurs when adding the rowframe – I assume for the same reason…not the last created frame.
Short of rebuilding the entire form ever time MLBFrameA needs an updated list – which I suspect would cause the repainting issues I mentioned previously – or perhaps adding a number of blank rows to the original .list of MLBFrameA that than then be updated using .caption etc…..I’m out of ideas.
Any thoughts on how to get around this VBA bug at all? I know it’s not an MLB issue but thought you may have a bright idea on how to creatively code around the VBA bug.
Thanks
Josh
Hi Josh,
I haven’t looked at it in a while. The way I got around that bug was to create a new frame each time the list was updated. Not ideal, but the easiest way around it.
-Paul
I’ve tried your great “library” but even if I’ve added correctly the Modern Listbox AddIn.xlam in the C:\Users\xxxxxxx\AppData\Roaming\Microsoft\AddIns when I run Demo i Got error in the following part of code:
Set searchBox = m_parentFrame.parentFrame.Parent.Controls(m_controlNameHelpers.BuildSearchTextboxName(ID))
If i skip the error (2 times), moving forward the code, I got the error:
m_parentFrameManager.parentFrame.Controls.Remove m_nameHelper.BuildHeaderFrameName()
Skipping also this error I got again the previous error for 4/5 time, and at the end the Form is shown coirrectly and seems works fine, but I’d avoid these errors if it is possible
What error are you getting?
errore di run-rime -2147024809 “Impossibile trovare l’oggetto specificato”
I think it correspond to something like this “The specified object could not be found!.
The debug hihglight “Set searchBox = ….” line the following function
Public Function CheckSearchBoxExists(ID As Long) As Boolean
On Error Resume Next
Dim searchBox As MSForms.TextBox
Set searchBox = m_parentFrame.parentFrame.Parent.Controls(m_controlNameHelpers.BuildSearchTextboxName(ID))
CheckSearchBoxExists = Not searchBox Is Nothing
End Function
It is related to this line in the From Initialize
CheckBoxAutoWidth.Value = True
If I cokemnt it I gor another error: “The Argument is not valid” on line
m_parentFrameManager
in the function
Private Sub RemoveHeaderControls()
On Error Resume Next ‘ header controls may not exist
‘ If code stops here then make sure “Break on all errors” is not turned on in options
m_parentFrameManager.parentFrame.Controls.Remove m_nameHelper.BuildHeaderFrameName()
End Sub
and it seems related to -list in the from Initialize
If a search for it in the call stack, it seems coming from “Form Initialize” at row:
CheckBoxAutoWidth.Value = True
If I coment it the debug stop with error “The Agrument is not valid” at the line:
m_parentFrameManager.parentFrame.Controls.Remove m_nameHelper.BuildHeaderFrameName()
Contained in the function
Private Sub RemoveHeaderControls()
On Error Resume Next ‘ header controls may not exist
‘ If code stops here then make sure “Break on all errors” is not turned on in options
m_parentFrameManager.parentFrame.Controls.Remove m_nameHelper.BuildHeaderFrameName()
End Sub
And here the call stack shows it coming from line
.List = rg.Value
Always in the Form Initialize
Skipping it, the Form is shown and all seems works fine, even if cliccking on “Auto resize width” or Changins “Records per Page” rise the error in CheckPaginationExists Funcion in the Set SearchBox line
Which version of Excel are you using?
Office365
Hi Massimo.
I see what your problem is. Under Tools->Options and then General tab, look at the Error Trapping section. You have “Break on all Errors” selected. This should be set to “Break on unhandled Errors”. We only use “Break on all errors” when we are trying to locate a specific error.
-Paul
-Paul
Thanks a lot, changing the Error Trapping to unhanlded error solved the issue
Hi Paul, just one more question.
I have a table with all the currency values in an Excel spreadsheet. Each cell has the € character, a thousands separator, and two decimal places.
When the list box appears, I see the number like this: 65711.98
As soon as I sort any column, all the values are display ed correctly.
For example: 65,711.98 €
Is it possible to achieve the same behavior when the table is instantiated?
Hi Massimo,
That is an issue that I need to work on.
However, there is simple workaround. Sort the data before you add it to the listbox like this:
-Paul
Thanks a lot Paul, anyway it is a great tool!!
Hi,
Very appreciative of your work, Paul, particularly with the searchable dropdown. I have struggled trying to get the ComboBox and another form of searchable list box to work with changes I wanted, but it was just too frustrating and excruciating. Google’s AI-Mode was helpful up to a point, but often takes you down rabbit holes with one solution after another that never work.
QUESTION: With the searchable dropdown, launched from an active cell in the spreadsheet, I want to populate the textbox initially with the value in the active cell, if one is there. I can’t seem to do it. (I stuff the selected text from the dropdown into the active cell when the form goes away.) I tried this in my sheet’s _SelectionChange method but it doesn’t work. (I made the oEventHandler declaration public to access it from the sheet module.)
Load frmSearchableDropdown
frmSearchableDropdown.oEventHandler.StartText = ActiveCell.value
frm.show
Any ideas about how to do this?
Problems installing. I have Microsoft 365. I copy the .xlam file to the user file …………….
When I press /developer/excel/addins, select modern listbox I get:
This file type is not supported in Protected View. [ok]
and it isn’t available. It is followed by:
Microsoft Excel cannot access the file: C\Uses\widel\AppData\Roaming\Microsoft\Addins\Modern Listbox Addin.xlam’. There are several possible reasons.
* The file name or path does not exist.
* The file is being used by another program.
* the workbook you are trying to save has the same name as a currently open workbook.
[ok]
The worksheet that I’m getting the data from to fill the Modern Listbox cell values has different background colors to identify the values – instead of a numeric or text value. For example:
In (1,1), the background color is light grey.
In (1,2), the background color is white.
In (1,3), the background color is white.
In (2,1), the background color is white.
In (2,2), the background color is light grey.
In (2,3), the background color is light grey.
Is there a way to set the background colors in the Modern ListBox to the background colors from the worksheet range?
Hi Steve,
You mean to color individual cells? At the moment, there isn’t any way to do it.
-Paul
Hi John,
How can i uninstall de modernlistbox?
You can remove the file from the AddIns folder while Excel is closed: C:\Users\[username]\AppData\Roaming\Microsoft\AddIns
-Paul
I can use the .ListCount to get the number of records in a regular ListBox. How do I get the number of records in the ModernListbox?
There isn’t code for that now but you can add the code below to the clsModernlistBox class
Public Property Get RowCount() As Long
ColumnCount = GetDataRowCount()
End Property
Much appreciated
Is it possible to capture the change event of the modernlistbox?
There are events for select, deselect and double click. What would change relate to?
The header checkbox to select all or clear selection. In my case a listbox column has a number field for which I am showing a aggregate on the form of the selected items. Triggering this in the m_modernlistbox_itemselected(row) sub. However when the header checkbox is clicked it is not affecting the itemselected procedure so the aggregate is not updating to show Zero when cleared nor total of all when all is selected. Hope I am making sense?