To check the Textbox Value in combobox. Split its value from combobox and Range address to display in Another Texbox

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
577
Hello I get the Names and its range displayed in combobox with below code, want to check if the Name typed in textbox1 should however match the name in combobox with msgbox displayed "Exists" and its range displayed in another Textbox eg. Textbox2
I am unable to get the Range Address in Textbox2

The List displayed in ComboBox1 as follows ie with Name and Range
596501 A2:AI6
102603 A7:AI9
NIM-K-102 556304 A10:AI15 ' The Name here is different with space in between

Code:
Sub GetNames()
  Dim Ray() As String
  Dim c As Range, LastA As Range
  Dim rws As Long, k As Long
  
  Set LastA = Range("A" & Range("AC" & Rows.Count).End(xlUp).Row)
  For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
    rws = 1
    If IsEmpty(c.Offset(1).Value) And c.Address <> LastA.Address Then rws = rws + Range(c, LastA).SpecialCells(xlBlanks).Areas(1).Rows.Count
    k = k + 1
    ReDim Preserve Ray(1 To k)
    Ray(k) = c.Value & " " & c.Resize(rws, 29).Address(0, 0)
  Next c
  ComboBox1.List = Ray
End Sub


Private Sub cmdBtnGetNames_Click()
Dim i as integer
Call GetNames


For i = 0 To ComboBox1.ListCount - 1
    If  ComboBox1.List(i) =Textbox1.Text   Then
       MsgBox ComboBox1.List(i) & " Exists"
       Textbox2.text = Split(ComboBox1.List(i) , " ")(1)
     End If
   Exit For
Next i
End Sub
Thanks NimishK
 
Last edited:

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
577
How can i get the Range Address in Textbox2

May be Attached file will have more clarity

https://www.dropbox.com/s/hp5pp8ji336wfud/ComboSrch-TxtbxValue-XLMR.xlsm?dl=0

FYI in Post #1
Set LastA = Range("A" & Range("AC" & Rows.Count).End(xlUp).Row)
is changed to
Set LastA = RgsWs.Range("A" & Range("E" & Rows.Count).End(xlUp).Row)
and
Ray(k) = c.Value & " " & c.Resize(rws, 29).Address(0, 0)
to
Ray(k) = c.Value & " " & c.Resize(rws, 5).Address(0, 0)



Thanks
NimishK
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,947
Office Version
365
Platform
Windows
Using Split is made complicated because some of your names contain spaces and others do not
- what is consistent is that the LAST space in value in ComboBox1 is ALWAYS followed by the range that you want in Textbox2

One way to arrive at the values you want
- place in a standard module to test and then apply the method inside code in the userform
Code:
Sub SplitMyValue()
    Const v = vbCr & vbCr
    Dim cb As String, Nm As String, Addr As String
    
    cb = "NIM-K-102 556304 A10:E15"
    Addr = Split(cb, " ")(UBound(Split(cb, " ")))
    Nm = Replace(cb, " " & Addr, "")
    MsgBox cb & v & Nm & v & Addr
    
    cb = "596501 A2:E6"
    Addr = Split(cb, " ")(UBound(Split(cb, " ")))
    Nm = Replace(cb, " " & Addr, "")
    MsgBox cb & v & Nm & v & Addr
End Sub
 

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
577
Hi Yongle
Indeed this was really great one. :)(y). Thanks a tonne
Using Split command was the only way i had thought of though complicated. But now charmingly Easy.
and Replace command is something new to me which i was never aware of.

so now having known the values of cb, Nm and Addr. How could i incorporate the same i below code
i.e in Textbox1 will be always typing the values of Nm
combobox1.List will be displayed as cb Values
and to get addr in textbox2

So as per below code when typing Nm value in textbox1.text and when pressing enter to match with Cb ie Combobox1.List.
msgbox 'Exists' and textbox2.Text to display the respective range address
Code:
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim i As Integer


If KeyCode = 13 Then


For i = 0 To ComboBox1.ListCount - 1
    If ComboBox1.List(i) = TextBox1.Text Then
       MsgBox ComboBox1.List(i) & " Exists"
       TextBox2.Text = Split(ComboBox1.List(i), " ")(1)
     End If
   'Exit For
Next i
End If


End Sub
NimishK
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,947
Office Version
365
Platform
Windows
Is this a valid test?
Code:
If ComboBox1.List(i) = TextBox1.Text Then
ComboBox.List(i) looks like this NIM-K-102 556304 A10:E15
TextBox1.Text looks like this TextBox1.Text
So the test will always return False

Is this the correct test?
Code:
    cb = ComboBox.List(i)
    Addr = Split(cb, " ")(UBound(Split(cb, " ")))
    Nm = Replace(cb, " " & Addr, "")
   [COLOR=#ff0000] If Nm = TextBox1.Text[/COLOR] Then
Or have I misunderstood what goes in TextBox1?
 
Last edited:

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
577
Is this the correct test?
Code:
    cb = ComboBox.List(i)
    Addr = Split(cb, " ")(UBound(Split(cb, " ")))
    Nm = Replace(cb, " " & Addr, "")
    [B][COLOR=#ff0000]If Nm = TextBox1.Text Then[/COLOR][/B]
Or have I misunderstood what goes in TextBox1?
This was indeed the Correct Test Dear
Thanks a Tonne:wink:(y)
 
Last edited:

Forum statistics

Threads
1,085,718
Messages
5,385,450
Members
401,955
Latest member
Yendorian

Some videos you may like

This Week's Hot Topics

Top