Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste text from a ComboBox into a cell
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: Hi Kevin, 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste text from a ComboBox into a cell
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 -- Regards, Tom Ogilvy Kevin wrote in message ... 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: Hi Kevin, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste text from a ComboBox into a cell
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. -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... 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 -- Regards, Tom Ogilvy Kevin wrote in message ... 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: Hi Kevin, 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste text from a ComboBox into a cell
Some day I'll actually read the questions.....
Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... 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 -- Regards, Tom Ogilvy Kevin wrote in message ... 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: Hi Kevin, 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cell contents, then paste into the same cell with other text. | Excel Discussion (Misc queries) | |||
Extract Cell Comments and Paste as text in a cell | Excel Worksheet Functions | |||
how to cut part of a text from one cell and automatically paste itonto another cell | Excel Discussion (Misc queries) | |||
Cutting text from one cell to paste in another? | Excel Worksheet Functions | |||
How do I paste text data into ONE cell only? | Excel Discussion (Misc queries) |