Spliting value of Combobox

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
577
Re: Spliting value of Combobox : Not working smoothly

Excellent Fluff A Perfect One

Any Ideas how can i have Right-Side Values of following Arr Values in Textbox when Clicking on ComboBox

Code:
Arr = Array("abc def", "ghi jkl", "mno pqr")
 
Last edited:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,109
Office Version
365
Platform
Windows
Re: Spliting value of Combobox : Not working smoothly

The easiest way is not to split the values in the first place, but put the complete value in the combobox.
With your real data could you ever have the same Right-side value repeated?
Also how many values are you likely to have in the combo?
 
Last edited:

ldonkers

New Member
Joined
Sep 26, 2018
Messages
8
Re: Spliting value of Combobox : Not working smoothly

Hello,

The following should work. Please note I have created a named range called "LiST" on the first worksheet where the original list is stored.

You can also replace LIST with your actual range... Range("I17:I19").Value for example.



Private Sub UserForm_Initialize()



ComboBox1.List = GetFirst(Range("LiST").Value) 'named range here


End Sub


Function GetFirst(lst)



Dim tempName() As String 'temp array to hold the split values


Dim i As Long

For i = 1 To UBound(lst)
tempName = Split(lst(i, 1), " ") 'split the name by the spaces

lst(i, 1) = tempName(0) 'use 0 to return the only first part back to list

Next i


GetFirst = lst ' returns the modified list


End Function
 

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
577
Re: Spliting value of Combobox : Not working smoothly

Idonkers Welcome to the Forum
Thanks for your new suggestion of GetFirst() Function
 

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
577
Re: Spliting value of Combobox : Not working smoothly

Fluff
The easiest way is not to split the values in the first place, but put the complete value in the combobox.
Ok

With your real data could you ever have the same Right-side value repeated?
Really I don't know what if repeated and what if not repeated

Also how many values are you likely to have in the combo?
I dont know may be uptil Ubound(arr) ie if i use 'Arr = Array("abc def", "ghi jkl", "mno pqr")
or
arr = Worksheets("Sheet1").Range("A2").CurrentRegion.Value
Bit confused
 
Last edited:

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
577
Re: Spliting value of Combobox : Not working smoothly

Tried Coding 1 and Coding 2
Here you need to Put Full Values of Combo Box.
Not at all Happy with Achievement with Coding 1. will really appreciate if improvements can be done else this is very small but with unstructured representation
Infact not happy getting Left and Right Values in ComboBox and in Textbox we show Right Values
Code:
'''Coding 1
Private Sub UserForm_Initialize()
Dim Arr As Variant
Arr = Array("abc def", "ghi jkl", "mno pqr")
   For i = 0 To UBound(Arr)
      ComboBox2.AddItem Arr(i)
  Next i
End Sub

Private Sub ComboBox2_Click()
 Dim idx As Long
 idx = ComboBox2.ListIndex
 If idx <> -1 Then
   TextBox3.Value = Split(ComboBox2.Value, " ")(1)
 End If
End Sub
Success with coding 2
Actually You dont require to Put Full combobox Value you get left values of ComboBox i.e when you get Ary values from Column of the worksheet
Code:
''Coding 2
Private Sub UserForm_Initialize()
   Dim ary As Variant
   Dim i As Long
   ary = Worksheets("Sheet1").Range("A2").CurrentRegion.Value
   For i = 1 To UBound(ary)
   ComboBox1.AddItem Split(ary(i, 1))(0)
   Next i
End Sub

Private Sub Combobox1_Click()
 Dim idx As Long
  idx = ComboBox1.ListIndex
       If idx <> -1 Then
            TextBox1.Value = Split(Worksheets("Sheet1").Range("A" & idx + 2).Value, " ")(1)
       End If
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,424
Messages
5,414,383
Members
403,526
Latest member
swedeness50

This Week's Hot Topics

Top