Userforms - listbox question

[Stuart]

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.

 

[Tom Ogilvy]

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).

 

[Stuart]

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.

 

[Stuart]

Nope!

Regards and many thanks.

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.