List Box: How to copy clicked items on current sheet active cell?

[poli9]

Hi,

I'm running Excel 2021.

I have a created a "List Box" (from Developer/Insert/Form Controls/List Box) on Sheet1.
It is filled with a list of values from Sheet2 :

Every time I click on an item in this List Box, I want my current cell (I mean the active one where my cursor is in the sheet) to be filled in with the List Box clicked item and then, move down (the new active cell in Sheet1 becomes the next one below, ready to be filled in with a new List Box clicked item).

I also want this List box to remain at the same place even if I scroll Sheet1's window bars. (except I decide to move it (left click on it + right click to move it) )

How can I do this?

Thanks for your help.

 

[DanteAmor]

Every time I click on an item in this List Box, I want my current cell (I mean the active one where my cursor is in the sheet) to be filled in with the List Box clicked item and then, move down (the new active cell in Sheet1 becomes the next one below, ready to be filled in with a new List Box clicked item).

For that part assign the following macro to your List Box (right-click on it, select Assign Macro, select MyListbox_Click and click OK.

Note: In this line Set shp = ActiveSheet.Shapes("List Box") of the macro fit to the name of your List Box
Put the following code in a module:

Sub MyListbox_Click()
  Dim shp As Shape
  Dim i As Long
 
  Set shp = ActiveSheet.Shapes("List Box")    'fit to the name of your listbox
  With shp.OLEFormat.Object
    For i = 1 To .ListCount
      If .Selected(i) Then
        ActiveCell.Value = .List(i)
        ActiveCell.Offset(1).Select
        Exit For
      End If
    Next
  End With
End Sub

I also want this List box to remain at the same place even if I scroll Sheet1's window bars. (except I decide to move it (left click on it + right click to move it) )

For that part, look for how to float the shape on the sheet
For example:

Cool Class for making Floating Shapes !

Hi, I coded this class recently and thought I would post it here. Workbook demo. Basically, it is just a standard userform (manipulated with the windows API) that implements a custom Interface (IFloatingShape) for easy & flexible use. One good thing about this API based class is that it...

www.mrexcel.com

Create floating shape that stays visible on top of page when scrolling

Hi, I have a button on top of each sheet and when clicked takes the user back to the TOC. 'Create & Position Shape Set Shape = Worksheet.Shapes.AddShape(msoShapeRoundedRectangle, 550, 0, 40, 20) I've added following code so that the shape stays in the exact same position when the user...

www.mrexcel.com

 

[poli9]

thanks!!!!!