Searching Value in ComboBox as per comboBox Columns

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
193
Hello
I would like to search values of Combobox Column values
For eg typing J in comboBox column 1
it should dropdown the list with all J and respective range in column2
Now if i type A or D or : in column 2 dropdown list to display all the names and and range
if typed Specificaly the Range A36: D42 in col2 of combobox then DropDown to display Steve A36:D42

What to code inorder to run the below following properly
1. Search Value Column wise as explained above
2. Combobox to display the Searched Name and respective Range or Searching Range in col 2 to display its respective Name in Column 1
3. After Searching, Selecting the item, Clicking or Pressing Enter to get the values in respective textbox

The Following code when executed
1. Combobox takes the full List of Names and Ranges
2. When clicked on DropDown Arrow and selecting an item it displays the Name only the Range section disappears
3. When Pressed Enter with Names shown(only) in combo List it executes Combobox_Exit Event
Code:
Private Sub UserForm_Initialize()
   Dim Rng As Range
   
   For Each Rng In Sheet1.Range("B:B").SpecialCells(xlConstants).Areas
      With Me.ComboBox1
         .AddItem Rng(1).Value
         .List(.ListCount - 1, 1) = Rng.CurrentRegion.Address(0, 0)
      End With
   Next Rng
   Me.ComboBox1.ColumnCount = -1
   Me.ComboBox1.ColumnWidths = "50;50"
End Sub

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   Dim Fnd As Range
   Dim Rw As Long
   
   Set Fnd = Sheet1.Range("B:B").Find(me.ComboBox1.text, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      With Fnd.CurrentRegion
         If .Cells(.Rows.Count, 2) = "Balance :" Then Rw = .Rows.Count - 1 Else Rw = .Rows.Count
         Me.txtPymnt = .Cells(Rw, 3)
         Me.txtPymtRcd = .Cells(Rw, 4)
         Me.txtRngAdd = .Address(0, 0)
         Me.txtRngFrom = Split(.Address(0, 0), ":")(0)
         Me.txtRngTo = Split(.Address(0, 0), ":")(1)
         If .Cells(Rw + 1, 3) <> "" Then
            Me.txtBalance = .Cells(Rw + 1, 3)
            Me.lblBal.Caption = "Balance : To be Paid"
         ElseIf .Cells(Rw + 1, 4) <> "" Then
            Me.txtBalance = .Cells(Rw + 1, 4)
            Me.lblBal.Caption = "Balance : To be Recieved"
         Else
            Me.txtBalance = ""
            Me.lblBal.Caption = "Balance"
         End If
      End With
   End If
End Sub

ABCD
1Individual Name:Jerry
2Transaction DateDescriptionPayments MadePayments Received
302-04-2015vchr no : 2600
4sfdfdsf
503-04-2015vchr no : 3200
6rerrt
704-04-2015Agst vchr no : 2 600
8rwewrew
9Total800600
10Balance :200
11
12Individual Name:Mike
13Transaction DateDescriptionPayments MadePayments Received
1402-04-2015vchr no : 41750
15gfhfhgfhgf
1603-04-2015Agst vchr no : 4 350
17oipoiopo
18Total1750350
19Balance :1400
20
21Individual Name:Kate
22Transaction DateDescriptionPayments MadePayments Received
2301-04-2015vchr no : 51500
24dfgfgff
2505-04-2015vchr no : 6600
26fsfdsfds
2706-04-2015Agst vchr no : 122000
28fdfds
2909-04-2015Agst vchr no : 51500
30ggfgfgfg
3110-04-2015Agst vchr no : 2 300
32fdfdsfds
33Total21003800
34Balance :1700
35
36Individual Name:Steve
37Transaction DateDescriptionPayments MadePayments Received
3802-04-2015vchr no : 41235
39dfdsfds
4003-04-2015Agst vchr no : 4 1235
41fdfdsf
42Total12351235
43

<tbody>
</tbody>


SamD
 
Last edited:

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,127,333
Messages
5,624,066
Members
416,010
Latest member
NJT

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
Top