Insert ListBox Selection into a cell on spreadsheet

[Anarchus]

Hi,

I have a listbox with the alphabet in it. Is there a way that when the letter is clicked on, that letter is inserted into a specific cell on a spreadsheet?

So when clicking on "A", the letter A is inserted into worksheet 6 (AusPost) cell $b$3, then if I click on the letter "M", $b$3 changes to the letter M.

[LJMetzger]

Hi Anarchus,

There are 3 different types of ListBox:
a. SpreadSheet 'Active X' ListBox (can get contents using Formula or VBA code)
b. SpreadSheet Forms ListBox (can get contents using Formula or VBA code)
c. UserForm ListBox (can get contents using VBA code only)

In all 3 cases, the ListBox choices are linked to the cells A1 thru A26.
The Forms ListBox selection is linked to cell 'F5' (returned as an index number). The Forms ListBox is assigned to Macro FormsListBoxEventHandler() which gets activated each time the Forms ListBox changes value.

The 'Active X' ListBox selection is linked to cell 'F17'. The 'Active X 'ListBox is assigned to Macro ListBox1_Change() in the 'Sheet'1 code module which gets activated each time the Forms ListBox changes value.

Please NOTE that 'Active X' controls do not play well with others, and can cause problems and worksheet corruption especially if hiding rows/columns and/or moving the workbook to a computer that uses a different version of Excel.


You did not specify which you are using, so the attached file has examples for all 3 using both formulas and code as appropriate:

'Active X' code in the Sheet1 Code Module:

Please Login or Register  to view this content.

Forms ListBox Code in Ordinary Code Module Module1:

Please Login or Register  to view this content.

UserForm Activation code in Ordinary Code Module Module1:

Please Login or Register  to view this content.

UserForm1 Code Module Code:

Please Login or Register  to view this content.

Lewis