Spliting value of Combobox

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
684
Anyone has idea to Split the value of Combobox.Text with smoother functioning
I tried the following
Code:
Private Sub UserForm_Initialize()
   Combobox1.List = Worksheets("Sheet1").Range("A2").CurrentRegion.Offset(1).Value
End Sub

Private Sub Combobox1_Click()

  Dim splitValue() As String
  Dim comboListName As String
  Dim idx As Long

comboListName = Combobox1.Text 

 idx = Combobox1.ListIndex
 splitValue = Split(comboListName, " ")
 Combobox1.Text =splitValue(0) 
''''Basically to read value of 1st most left values ie why i used splitvalue(0)
If idx <> -1 Then
ComboBox1.Text = splitValue(0)
End If
End Sub

Combobox1.Drop button style = 1

Got Stuck with above coding only and not moved further on
As it displays the two Separate values in ComboBox
When Clicked with Down Arrow Key button it only displays 1st value and really cannot go further down
Also have to select the value when dropdown. Really Event is not smooth triggering
With selection on the particular item it shows that single value but with down arrow key not happening

Regards
NimishK
 
Last edited:
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:
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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:
Upvote 0
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
 
Upvote 0
Re: Spliting value of Combobox : Not working smoothly

Idonkers Welcome to the Forum
Thanks for your new suggestion of GetFirst() Function
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top