Jump to content


Photo

Excel Multiple TextBoxs


  • Please log in to reply
2 replies to this topic

#1 JPL

JPL

    Apprentice

  • Quark
  • 108 posts

Posted 30 June 2017 - 06:29 PM

Hi ya, just wondering if anyone could help. I've created a user-form with multiple textboxes. One textbox has a listed names and when clicking on one name i want to get another textbox reflect information detailed on sheet2 (which is called Prefill_Values) of the excel workbook. I seem to have it slightly working. When i click on the first name the second textbox details the info but there's another 3rd textbox which should reflect different info but shows the same info as the 2nd. 

 

  'Fill TeamListBox (only needs to be done once)
    With TeamListBox
        .AddItem "Name"
        .AddItem "Name"
        .AddItem "Name"
    End With

    form_filled = False

End Sub

 

Private Sub TeamListBox_Click()
'This function runs when one of the options in the team list box is selected.
'It prefills the form with the parameters for that team, using the prefill_team function, with syntax
    Dim column As Integer
    
    If TeamListBox.Value = "Name" Then
        column = 6
    ElseIf TeamListBox.Value = "Name" Then
        column = 7
    ElseIf TeamListBox.Value = "Name" Then
        column = 8
    Else
        column = 9
        
    End If
    
    For ii = 1 To NumberStaffCat
        Me.Controls("StaffNumbers" & CStr(ii) & "SpinButton") = Sheets("Prefill_Values").Cells(ii + 1, column)
        Me.Controls("StaffNumbers" & CStr(ii) & "TextBox") = Sheets("Prefill_Values").Cells(ii + 1, column)
    Next ii
    
    For ii = 1 To NumberFleetItems
        Me.Controls("FleetNumbers" & CStr(ii) & "SpinButton") = Sheets("Prefill_Values").Cells(ii + 1, column)
        Me.Controls("FleetNumbers" & CStr(ii) & "TextBox") = Sheets("Prefill_Values").Cells(ii + 1, column)
    Next ii
    
    form_filled = True

End Sub

 

The info on Sheet2 is spread over 20 columns but i need to pick out specific one.

 

anyone have any ideas i could use?



#2 galdarian

galdarian

    Master

  • Atomican
  • 750 posts

Posted 03 July 2017 - 02:30 PM

Several ideas however to give you the best advice I would really have to understand the structure of the data you have on "Prefill_Values". Now, I'm assuming when you say "Text box" you mean a combobox or a listbox which are fairly interchangeable in terms of vba code. One thing you may / may not know about them is you can have multiple columns of data in the list even if they're not shown to the end user. In this way we can add a second column of data to the first combobox / listbox when the userform is initialised.

 

We can also use columns on the "Prefill_Values" sheet to load the first combobox. So for example if you use the first 2 columns ("A:B") to populate the TeamNames and the column of data you need later respectively. Then when the user changes the value of the first combobox you just use the hidden value to (re)load the list of the second and third comboboxes / listboxes. If you then need to highlight something in those lists then you can set the .listindex appropriately.

 

Code below shows you an example.

Option Explicit

Const M_strREFSHEET As String = "Prefill_Values"

Private Sub UserForm_Initialize()
    Dim rngR As Range

    Set rngR = ThisWorkbook.Worksheets(M_strREFSHEET).Cells(1, 1)
    
    Do
        Me.cbx1.AddItem rngR
        Me.cbx1.List(Me.cbx1.ListCount - 1, 1) = rngR.Offset(0, 1)      'Use the columns of the combobox or listbox as the value
        
        Set rngR = rngR.Offset(1, 0)
    Loop Until rngR = ""
End Sub

Private Sub cbx1_Change()
    Dim rngR As Range

    'Use the second column of the combobox to set the range to fill the new list of value in the other listbox / combobox
    Set rngR = ThisWorkbook.Worksheets(M_strREFSHEET).Cells(1, Me.cbx1.List(Me.cbx1.ListIndex, 1))
    
    'clear the lists first
    Me.lbx2.Clear
    Me.lbx3.Clear
    
    Do  'Fill the boxes with new values
        Me.lbx2.AddItem rngR
        Me.lbx3.AddItem rngR
        
        Set rngR = rngR.Offset(1, 0)
    Loop Until rngR = ""
End Sub

Now that assumes that I've understood your problem correctly :-s YMMV

 

 

edit: Tried to fix the highlighting


Edited by galdarian, 03 July 2017 - 02:34 PM.


#3 JPL

JPL

    Apprentice

  • Quark
  • 108 posts

Posted 04 July 2017 - 03:59 PM

Thanks Galdarian, i'll give your example ago. Yep you are correct, it's listbox not textbox. Basically i have userform with a listbox linked to columns on the Prefill_Values where the columns are titled by names and contain various numbers below them. The userform also has Multiple pages so when i click on a name in the listbox, the information under their names on the Prefill_Values sheet pops into the combobox on the relevant multipages. The columns titled by name are duplicates with different info under each so the info under one name pops into one combobox on one multipage and the same name with different info pops into another combobox on a different multipage. Hope that's not to confusing.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users