VBA & Macro Problems

Create a Pop-Up Customer List in an Excel Cell with VBA

Kushani NimanthikaKushani Nimanthika Nov 14, 2026 868 views

Question details

Create a temporary customer-selection list beside a selected Excel cell and insert the chosen name into that cell.

Create a Pop-Up Customer List in an Excel Cell with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user selects a cell in column B and wants to choose a customer from a pop-up list.
Observed behavior
The list should appear beside the selected cell, write the chosen customer name into it, and then disappear.
Before you start

Use desktop Excel on Windows for the ActiveX method, and make sure the Developer tab is visible. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and enable macros and ActiveX controls only for workbooks you trust.

Solution 1Recommended

Show an ActiveX customer list with worksheet VBA

Use this method when you want a list box to appear beside a selected cell in column B.

1
Insert the list box

On the target worksheet, select Developer > Insert > ActiveX Controls > List Box, then draw the control. With Developer > Design Mode enabled, open Properties, set its (Name) to ListBox1, and set Visible to False.

2
Add the worksheet code

Right-click the worksheet tab, select View Code, and paste this code into that worksheet's code window: Option Explicit Private mTarget As Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim box As OLEObject Set box = Me.OLEObjects("ListBox1") box.Visible = False Set mTarget = Nothing If Target.CountLarge <> 1 Then Exit Sub If Intersect(Target, Me.Columns("B")) Is Nothing Then Exit Sub With box.Object .Clear .AddItem "Acme Corp" .AddItem "Contoso" .AddItem "Northwind Traders" End With Set mTarget = Target box.Left = Target.Left + Target.Width box.Top = Target.Top box.Width = 120 box.Height = 90 box.Visible = True End Sub Private Sub ListBox1_Click() If mTarget Is Nothing Then Exit Sub If Me.ListBox1.ListIndex < 0 Then Exit Sub mTarget.Value = Me.ListBox1.Value Me.OLEObjects("ListBox1").Visible = False Set mTarget = Nothing End Sub

3
Customize and test

Replace the names in the AddItem lines with your customer names. Save the .xlsm file, turn off Developer > Design Mode, select a cell in column B, and click a customer in the list.

Show an ActiveX customer list with worksheet VBA
ActiveX availability: ActiveX controls require a supported Windows desktop version of Excel and may be disabled by security settings or organizational policy. Do not enable them for untrusted files.
Free Microsoft Office alternative

Manage customer lists with WPS Spreadsheet

WPS Office is a free, lightweight Office alternative with a familiar interface and Excel format compatibility, making it easy to move everyday spreadsheet work to WPS. The Excel ActiveX VBA solution itself requires supported Microsoft Excel.

Organize customer names in a spreadsheet and use selection lists for data entry.Open and edit Microsoft Excel formats such as XLSX.Navigate a familiar spreadsheet interface when moving from Excel.Use a lightweight, free office suite for everyday documents and spreadsheets.
microsoft office alternative - wps office

Frequently Asked Questions

Where do I paste the VBA code?

Right-click the tab of the worksheet containing the list box, select View Code, and paste the code into that worksheet's code window, not a standard module.

Why doesn't the list appear when I select a cell?

Check that you selected one cell in column B, that the control is named ListBox1 in Developer > Properties, and that Developer > Design Mode is off. Also check whether macros or ActiveX controls are blocked.

How do I change the customer names?

Open the worksheet code using the sheet tab's View Code option and replace the sample names in the .AddItem lines.

Can I use this ActiveX list in Excel for the web or on a Mac?

The ActiveX method requires supported desktop Excel on Windows. For a standard selection list without ActiveX, use Data > Data Validation > List where available.

What format should I use to save the VBA code?

Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). The standard .xlsx format does not retain VBA macros.