Paste text from a ComboBox into a cell

  • Thread starter Thread starter Kevin
  • Start date Start date
K

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
 
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
 
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.
 
Some day I'll actually read the questions.....


Tom said:
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.
Click to expand...
 
Back
Top