Userform with ActiveX ListBox - Use Named Range to Populate

[GeeWhiz7]

Hi Folks,
I have an active X listbox that likes to move positions around on my worksheet so I want to place it on a userform instead, but then that takes me out of my depth in knowing how to,

1) Populate the listbox selections with a Named Range
2) Return the number (ie Integer 3 for the 3rd item in the named range) of the selected name in the named range to a fixed cell in a worksheet.

All this is easy if the listbox is directly in the worksheet, but I can't seem to figure out how to use the named range to populate the one in the userform.

Thanks for your help,
-Will

 

[Jeffrey Mahoney]

I wrote some simple code that moved the listbox to a position related to a cell when the user selected a different value. You could easily use a named range for the location you want the listbox.

Private Sub ListBox1_Change()
  ListBox1.Left = Range("A2").Offset(0, 1).Left
  ListBox1.Top = Range("A2").Top
  
End Sub

 

[GeeWhiz7]

I wrote some simple code that moved the listbox to a position related to a cell when the user selected a different value. You could easily use a named range for the location you want the listbox.

Private Sub ListBox1_Change()
  ListBox1.Left = Range("A2").Offset(0, 1).Left
  ListBox1.Top = Range("A2").Top
 
End Sub

Thank you Jeffrey, this gets me closer for sure and now it will snap to a position based on click or other change.
One question, instead of forcing the positioning with the Change(), how can I simply lock it to one place and fixed size? I know how to do with Sheets, ie With Sheets().Range...
But I'm not sure how to select the Listbox itself.

 

[Jeffrey Mahoney]

I have tried so many ways to lock a position of a control (Object Positioning: Don't Move or Size with Cells) to no avail. They wonder like a dog following their nose. And just like a dog, repeat training is necessary. You can specify certain coordinates and you can have it correct it's position with a mouse move

Private Sub ListBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  ListBox1.Left = 20                 'Range("A2").Offset(0, 1).Left
  ListBox1.Top = 10                  'Range("A2").Top
End Sub

 

[Jeffrey Mahoney]

Selecting a listbox requires you to click the Design Mode button first.

 

[GeeWhiz7]

Selecting a listbox requires you to click the Design Mode button first.
View attachment 53125

Okay, let me try fixing the coordinates on a mouse click. I've read that putting the listbox inside a UserForm (in vba) will lock it in place, but then I don't know how to populate the listbox with a range name and return values as in my first post.

 

[Jeffrey Mahoney]

You have to load the values you want in a control like this:

Private Sub UserForm_Initialize()
  ListBox1.List = Sheets("Sheet9").Range("A2:A10").Value
End Sub

 

[GeeWhiz7]

You have to load the values you want in a control like this:

Private Sub UserForm_Initialize()
  ListBox1.List = Sheets("Sheet9").Range("A2:A10").Value
End Sub

It is working on the user form now with the named range...now to figure out the rest.
Thanks for your help Jeffrey!