Combo box help

[miket85]

Hey guys,

I had a question regarding a combo box search function. Ultimately what I am try to accomplish is to have a combo box where the user can which column to search and another box will return the result. It sounds pretty simple right? Currently I have the program running a search function perfectly (user enters a number and the second box displays the related number from column 1), I want to modify it so that it will only search the column they require, so it wont cycle through all the columns.

Let me knwo if you need my code thahnks!!!

 

[miket85]

anybody?

 

[miket85]

The following is my code, i want this to be incorporated into the drop down box. so instead of searching "B:Z" I want it to search which ever column the user selects.


Private Sub CommandButton1_Click()

Dim fnd As Range
Dim tbl As Range

Set tbl = Sheet169.Range("B:Z").CurrentRegion


Set fnd = tbl.Find(What:=TextBox3.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If fnd Is Nothing Then
MsgBox "No match found!"
TextBox3.Value = ""

Exit Sub

Else: fnd.Activate

End If


Label5.Caption = Cells(fnd.Row, "A")



End Sub

 

[miket85]

ttt

 

[miket85]

any ideas?

 

[miket85]

I am thinking i would like to do an if-else statement to select the range the user chooses when they select from the drop down box, but something is up when i try to implement the if then statements!

 

[miket85]

im open to other methods as well as long as they achieve the search column, i can use a list box, etc.

 

[kyndig]

The following is my code, i want this to be incorporated into the drop down box. so instead of searching "B:Z" I want it to search which ever column the user selects.


Private Sub CommandButton1_Click()

Dim fnd As Range
Dim tbl As Range

Set tbl = Sheet169.Range("B:Z").CurrentRegion


Set fnd = tbl.Find(What:=TextBox3.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If fnd Is Nothing Then
MsgBox "No match found!"
TextBox3.Value = ""

Exit Sub

Else: fnd.Activate

End If


Label5.Caption = Cells(fnd.Row, "A")



End Sub

Are you using a UserForm to get this information? That would be my approach:

I'd use a UserForm, and put the following controls on it:

ComboBox: [comboColumnList]
TextBox: [strSearchFor]
Button: [cmdSearch]
Button: [cmdCancel]

For the click command, open the form:

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

In the code on UserForm1, fill the combobox on load, something like:

Private Sub UserForm_Initialize()
    Dim rng As Range, cell As Range
    Dim sh As Worksheet
 
    Set sh = Worksheets("Sheet1")
    Set rng = sh.Range("A1:Z1")
 
    For Each cell In rng
        With UserForm1.comboColumnList
            .AddItem cell.Value
        End With
    Next cell
End Sub

Then put the find statement in the cmdSearch portion of the UserForm code to handle any msgbox output or moving the cursor location to the found value.

 

[miket85]

yeah thats pretty much what i am doing, give me a second i will supply my full code, have one combobox used for column selection, one input box for search function, on text box to display results, and one cmdbutton to run search, one cmdbutton to close the user form...

 

[miket85]

Private Sub ComboBox1_Change()
ComboBox1.List = Array("item 1", "item 2", "item 3")
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub CommandButton1_Click()

Dim fnd As Range ' this will be the samew as your entry in TextBox1
Dim tbl As Range ' this will be the table to search

***This part throwing errors***
If ComboBox1.Value = "item 1" Then Set tbl = Sheet169.Range("B").CurrentRegion

ElseIf ComboBox1.Value = "item 2" Then Set tbl = Sheet169.Range("C").CurrentRegion

Set fnd = tbl.Find(What:=TextBox3.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
'if the item is not found the user is informed and the textbox cleared
If fnd Is Nothing Then
MsgBox "No match found!"
TextBox3.Value = ""

Exit Sub

'if the item is found the details will be copied to the labels
Else: fnd.Activate

End If

'now put the information stored adjacent to the found item into label1,etc
Label5.Caption = Cells(fnd.Row, "A")



End Sub