Adding items from Listbox to empty textbox

[Corey ....]

I have a form with 24 textboxes on it.
The form has a populated listbox with data from a sheet.
I want to be able to have the user clcik on any item in the
listbox(Listbox1) and the selected value to be placed intoa Textbox.
But how do i get the value to be placed into the next available (empty)
Textbox from 1-24 ?


Corey....

 

[Patrick C. Simonds]

Try this:

Private Sub ListBox1_AfterUpdate()
If TextBox1.Value = "" Then
TextBox1.Value = ListBox1.Value
GoTo Finished
End If

If TextBox2.Value = "" Then
TextBox2.Value = ListBox1.Value
GoTo Finished
End If

If TextBox3.Value = "" Then
TextBox3.Value = ListBox1.Value
GoTo Finished
End If

' continue for the rest of the TextBoxes


Finished:
End Sub

 

[Dave Peterson]

If you name the textboxes nicely (TextBox1, Textbox2, ..., textbox24), you could
use code like:

Option Explicit
Private Sub ListBox1_Change()
Dim MaxNumberOfTextBoxes As Long
Dim AssignedTheValue As Boolean
Dim iCtr As Long

MaxNumberOfTextBoxes = 24

If Me.ListBox1.ListIndex < 0 Then
'nothing selected
Exit Sub
End If

AssignedTheValue = False
For iCtr = 1 To MaxNumberOfTextBoxes
With Me.Controls("Textbox" & iCtr)
If .Value = "" Then
AssignedTheValue = True
.Value = Me.ListBox1.Value
Exit For
End If
End With
Next iCtr

If AssignedTheValue = False Then
Beep
MsgBox "No space available--what should happen?"
End If

End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
.MultiSelect = fmMultiSelectSingle
For iCtr = 1 To 5
.AddItem "A" & iCtr
Next iCtr
End With
End Sub


But I think I wouldn't use the _Change event. I know as a user, I can make lots
of mistakes when trying to click on the correct item.

I'd use an "Ok" commandbutton on the form.

You can change:
Private Sub ListBox1_Change()
to:
Private Sub CommandButton1_Click()