Paste text from a ComboBox into a cell

[Kevin]

Some people here in the forum where trying to help me with a problem I
have, but I was incapable of fallowing their instruction, can anyone help
with step by step instructions, please, I'm sure I'm missing something very
simple!
I made a Combo Box from the Forms Menu and input a range of cells
(Notes!$A$2:$A$21) that contain a list of names. What I would like to do
is,to select a any cell and then go to the Combo Box select a Name and then
it would just copy that name to the previously selected cell. just a cut and
paste of the text, but I what the option of been able to just add to the
pasted text.
I think part of the problem is that is no a Control type list box but a
plain Forms(non-VBA) list and the help is for a Control box?

James S. was nice enough to help me with this code so I can add to my excel
sheet but I could not find the name of the Listbox
Here is the original email that James S. posted:

You can try the code below. You will need to do the
following:

1) Add the code below to workbook.
2) Right-click your list box and select "Assign Macro",
then select the Macro named "ReturnListBoxSelection".
3) I used the worksheet name "Notes", so if your
worksheet name is different you will need to change it.

Sub ReturnListBoxSelection()
Dim lbcf As ControlFormat

Set lbcf = ThisWorkbook.Worksheets("Notes").Shapes
("List Box 2").ControlFormat

ActiveCell.Value = lbcf.List(lbcf.ListIndex)
End Sub

also forgot to mention that you will need to change the
name of the ListBox "List Box 2" to the name of your
ListBox.

Regards,
James S

Thank you in advance for all your help!
Kevin Brenner

 

[Tom Ogilvy]

Kevin, this code will show you the names of the listboxes from the forms
menu

Sub showNames()
Dim lbox As ListBox
For Each lbox In ActiveSheet.ListBoxes
Set rng = lbox.TopLeftCell
MsgBox "Listbox over cell " & rng.Address & " is named " & lbox.Name
Next
End Sub

However, I have designed this so you don't need to know the name. Right
click on the list box and select assign macro. Assign this macro.

Sub Lbox_click()
Dim lbox As ListBox
sName = Application.Caller
Set lbox = ActiveSheet.ListBoxes(sName)
ActiveCell.Value = Trim(ActiveCell.Value & _
" " & lbox.List(lbox.ListIndex))
lbox.ListIndex = 0
End Sub

 

[Tom Ogilvy]

Sorry, you did say combobox and I used the listbox, but the code is pretty
similar:

Sub showdropdownNames()
Dim dbox As DropDown
For Each dbox In ActiveSheet.DropDowns
Set rng = dbox.TopLeftCell
MsgBox "Combobox over cell " & rng.Address & " is named " & dbox.Name
Next
End Sub

However, I have designed this so you don't need to know the name. Right
click on the list box and select assign macro. Assign this macro.

Sub dbox_click()
Dim dbox As DropDown
Dim sName As String
sName = Application.Caller
Set dbox = ActiveSheet.DropDowns(sName)
ActiveCell.Value = Trim(ActiveCell.Value & _
" " & dbox.List(dbox.ListIndex))
dbox.ListIndex = 0
End Sub


Dave has provided similar code, but his does not clear the dropdown box, so
you can't select the same name without selecting a different name first.

 

[Dave Peterson]

Some day I'll actually read the questions.....

Sorry, you did say combobox and I used the listbox, but the code is pretty
similar:

Sub showdropdownNames()
Dim dbox As DropDown
For Each dbox In ActiveSheet.DropDowns
Set rng = dbox.TopLeftCell
MsgBox "Combobox over cell " & rng.Address & " is named " & dbox.Name
Next
End Sub

However, I have designed this so you don't need to know the name. Right
click on the list box and select assign macro. Assign this macro.

Sub dbox_click()
Dim dbox As DropDown
Dim sName As String
sName = Application.Caller
Set dbox = ActiveSheet.DropDowns(sName)
ActiveCell.Value = Trim(ActiveCell.Value & _
" " & dbox.List(dbox.ListIndex))
dbox.ListIndex = 0
End Sub

Dave has provided similar code, but his does not clear the dropdown box, so
you can't select the same name without selecting a different name first.