Listbox on multiple sheets

Question

I have a workbook with many sheets.  I have created a ListBox that pulls information from another sheet.  I need this ListBox on many of the sheets.  How do I get this ListBox on all the sheets without having to insert it manual on every sheet?  I tried copy and paste but that just gives me an image not the actual ListBox to choose items.

Answer 1

You didn't say which type of Listbox you are using.

If it's from the Form Controls set, right-click it > Copy and then Paste to the other sheet.

If it's from the ActiveX Controls set, Developer > Design Mode > right-click it > Copy and then Paste Special > As Listbox Object on the other sheet.  However, that won't copy the ListFillRange or LinkedCell properties so you might want a macro to run through the sheets duplicating that.

I would recommend (as always) using the simpler Form Controls listbox if it will do the job you require.

The significant differences are that indexes start from 1 (not 0) and the linked cell shows the index value of the selected item, not the selected value itself.  You get to the value by =INDEX(ListRange, LinkedCell)

Alternatively you could consider a non-modal user form containing the listbox - so it just floats above the workbook ready for use with any sheet.