Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms - listbox question
I've a listbox populated with unique items, and in
ascending order.....basically A, B, C etc. These are the user's identifiers against data that they will want to sort/group by. How do I present a control alongside the listbox so that user can (eg) type their full value for 'A', please? So if 'A' were Timber, I could then create a new sheet (Timber) ready to receive all 'A's. Likewise for the remaining items in the listbox, Or am I using a sledgehammer, here? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms - listbox question
Just put a textbox next to the list box - each time the user selects an item
in the listbox, they can type in the corresponding full value in the textbox, then you can use an array or some other mechanism to make the association - perhaps using a second hidden column (or maybe even visible) in the listbox. In the latter case, if the user chose an item in the listbox, you code would first check if it already has a definition and put it in the textbox if it does. Private Sub Listbox1_Click() Textbox1.Text = Listbox1.Column(1,Listbox1.Listindex) End sub Private Sub Textbox1_AfterUpdate() Listbox1.column(1,Listbox1.ListIndex) = Textbox1.Text End Sub Of course the challenge here is if they are supposed to select an item in the listbox for some other purpose as well. You have to determine how this will work - will the enter all Full Values and then create sheets, or will the create sheets selectively and each time you want to prompt for the full value? If the latter, you could possibly just throw up an input box on the way to creating the sheet (but I would still store the value in the second column of the listbox I think). -- Regards, Tom Ogilvy Stuart wrote in message ... I've a listbox populated with unique items, and in ascending order.....basically A, B, C etc. These are the user's identifiers against data that they will want to sort/group by. How do I present a control alongside the listbox so that user can (eg) type their full value for 'A', please? So if 'A' were Timber, I could then create a new sheet (Timber) ready to receive all 'A's. Likewise for the remaining items in the listbox, Or am I using a sledgehammer, here? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms - listbox question
So your two subs are designed to reference the
entry in the textbox back to the listbox. and vice- versa? I put those subs in the form (there is no other code in the form) and ran the code from the module. The form showed with the listbox populated correctly and the textbox empty. Against 'A', I typed a typical real user value: 'Timber' I was not able to type any further entry in the textbox except if I clicked another item in the listbox. When I did, I was taken to the sub Private Sub Textbox1_AfterUpdate()and received the message: Could not set the Column property: Invalid property array index What am I missing, please? (aside from brains). Regards and thanks. "Tom Ogilvy" wrote in message ... Just put a textbox next to the list box - each time the user selects an item in the listbox, they can type in the corresponding full value in the textbox, then you can use an array or some other mechanism to make the association - perhaps using a second hidden column (or maybe even visible) in the listbox. In the latter case, if the user chose an item in the listbox, you code would first check if it already has a definition and put it in the textbox if it does. Private Sub Listbox1_Click() Textbox1.Text = Listbox1.Column(1,Listbox1.Listindex) End sub Private Sub Textbox1_AfterUpdate() Listbox1.column(1,Listbox1.ListIndex) = Textbox1.Text End Sub Of course the challenge here is if they are supposed to select an item in the listbox for some other purpose as well. You have to determine how this will work - will the enter all Full Values and then create sheets, or will the create sheets selectively and each time you want to prompt for the full value? If the latter, you could possibly just throw up an input box on the way to creating the sheet (but I would still store the value in the second column of the listbox I think). -- Regards, Tom Ogilvy Stuart wrote in message ... I've a listbox populated with unique items, and in ascending order.....basically A, B, C etc. These are the user's identifiers against data that they will want to sort/group by. How do I present a control alongside the listbox so that user can (eg) type their full value for 'A', please? So if 'A' were Timber, I could then create a new sheet (Timber) ready to receive all 'A's. Likewise for the remaining items in the listbox, Or am I using a sledgehammer, here? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms - listbox question
Nope!
Regards and many thanks. "Tom Ogilvy" wrote in message ... Did you set columncount to 2? And you are correct - as I stated, an entry is made in the textbox for the selected Item. In a new workbook, put in a userform with a listbox1 and a textbox1 paste in this code in the Userform module Option Explicit Private Sub Listbox1_Click() If ListBox1.ListIndex < -1 Then If ListBox1.Column(1, ListBox1.ListIndex) < "" Then TextBox1.Text = ListBox1.Column(1, ListBox1.ListIndex) TextBox1.SetFocus TextBox1.SelStart = 0 TextBox1.SelLength = Len(TextBox1.Text) Else TextBox1.Text = "" TextBox1.SetFocus End If Else TextBox1.Text = "" End If End Sub Private Sub Textbox1_AfterUpdate() If ListBox1.ListIndex < -1 Then If TextBox1.Text < "" Then ListBox1.Column(1, ListBox1.ListIndex) _ = TextBox1.Text End If End If End Sub Private Sub UserForm_Initialize() Dim i As Long ListBox1.Width = 92 ListBox1.ColumnCount = 2 ListBox1.ColumnWidths = "18;72" For i = 1 To 8 ListBox1.AddItem Chr(i + 64) Next i End Sub Play with that. -- Regards, Tom Ogilvy Stuart wrote in message ... So your two subs are designed to reference the entry in the textbox back to the listbox. and vice- versa? I put those subs in the form (there is no other code in the form) and ran the code from the module. The form showed with the listbox populated correctly and the textbox empty. Against 'A', I typed a typical real user value: 'Timber' I was not able to type any further entry in the textbox except if I clicked another item in the listbox. When I did, I was taken to the sub Private Sub Textbox1_AfterUpdate()and received the message: Could not set the Column property: Invalid property array index What am I missing, please? (aside from brains). Regards and thanks. "Tom Ogilvy" wrote in message ... Just put a textbox next to the list box - each time the user selects an item in the listbox, they can type in the corresponding full value in the textbox, then you can use an array or some other mechanism to make the association - perhaps using a second hidden column (or maybe even visible) in the listbox. In the latter case, if the user chose an item in the listbox, you code would first check if it already has a definition and put it in the textbox if it does. Private Sub Listbox1_Click() Textbox1.Text = Listbox1.Column(1,Listbox1.Listindex) End sub Private Sub Textbox1_AfterUpdate() Listbox1.column(1,Listbox1.ListIndex) = Textbox1.Text End Sub Of course the challenge here is if they are supposed to select an item in the listbox for some other purpose as well. You have to determine how this will work - will the enter all Full Values and then create sheets, or will the create sheets selectively and each time you want to prompt for the full value? If the latter, you could possibly just throw up an input box on the way to creating the sheet (but I would still store the value in the second column of the listbox I think). -- Regards, Tom Ogilvy Stuart wrote in message ... I've a listbox populated with unique items, and in ascending order.....basically A, B, C etc. These are the user's identifiers against data that they will want to sort/group by. How do I present a control alongside the listbox so that user can (eg) type their full value for 'A', please? So if 'A' were Timber, I could then create a new sheet (Timber) ready to receive all 'A's. Likewise for the remaining items in the listbox, Or am I using a sledgehammer, here? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Beginer to UserForms question on assigning properties to objects | Excel Discussion (Misc queries) | |||
ListBox Question | Excel Worksheet Functions | |||
Listbox Question | Excel Discussion (Misc queries) | |||
Listbox Question | Excel Worksheet Functions | |||
Listbox question | Excel Programming |