Spliting value of Combobox

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
580
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:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

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

Could you please explain what exactly you are trying to do, as it is not clear from your op.
 

NimishK

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

Well Fluff you need to copy the above and try it your end and Try clicking in ComboxBox_click()

Basically i am trying to seperate the values of Column A which has two values seperated by blank space and so the left value of column A trying to get on Combobox.
 
Last edited:

Fluff

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

What's the point of trying code that doesn't work, when I have no idea what you are trying to do? ;)
Are you trying to populate the combo with all the values from col A after you have split them?
 

NimishK

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

Are you trying to populate the combo with all the values from col A after you have split them?
Yes.
trying to get left value of column A into Combobox. but Combo not working as i desire
 
Last edited:

Fluff

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

Try
Code:
Private Sub UserForm_Initialize()
   Dim ary As Variant
   Dim i As Long
   ary = Worksheets("Sheet1").Range("A2").CurrentRegion.Value
   For i = 2 To UBound(ary)
   ComboBox1.AddItem Split(ary(i, 1))(0)
   Next i
End Sub
and remove the code from the combobox event
 

NimishK

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

That was really Excellent one (y)
Ok i tried the following below code too but i get Subscript out of range
Code:
Dim Arr As Variant
Arr = Array("abc def", "ghi jkl", "mno pqr")
Me.ComboBox1.Clear
Me.ComboBox1.List = Arr

 Dim i As Long
   For i = 1 To UBound(Arr)
   ComboBox1.AddItem Split(Arr(i, 1))(0)
   Next i
 
Last edited:

Fluff

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

Where is that code located & what are you trying to do?
 

NimishK

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

The code is in UF_Initialize.

I was trying the same thing to get Left values stored in Array in combobox.
Code:
Arr = Array("abc def", "ghi jkl", "mno pqr")
To get ABC
GHI
MNO
in combobox list
Your help will be appreciated
 

Fluff

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

Try
Code:
Dim Arr As Variant
Arr = Array("abc def", "ghi jkl", "mno pqr")
Me.ComboBox1.Clear

 Dim i As Long
   For i = 0 To UBound(Arr)
      ComboBox1.AddItem Split(Arr(i))(0)
   Next i
 

Watch MrExcel Video

Forum statistics

Threads
1,095,819
Messages
5,446,698
Members
405,413
Latest member
AlainCar

This Week's Hot Topics

Top