VBA find and return result to textbox?

[BungleNZ]

Hi,

Hopefully this should be an easy one - I just can't get it to work. In excel 2003 I'm trying to get a multiple search/find to return an offset text string to a text box (it will actually be multiple strings into multiple textboxes, but for the purpose of this question basing it on one should suffice).

The description of the search is along these lines: look for listbox1.value in dynamic range "Customers" - column A - , and look for combox1.value in dynamic sheet range "Contacts" - column G - and return cell entry in column H (has phone number) to textbox1.

I would put this code into the combobox1 change event so that when the contact name changes, a different phone number would appear.

My thoughts at the moment are that maybe the easiest way would be to get the cell address from combobox1, and just return the offset (0,1) value to the textbox? I'm just not sure how to do this.

 

[BungleNZ]

This is the code I've got so far, but I don't think it will work correctly. I think it would give an incorrect result if there happened to be two (or more) different contacts with the same name (I want to have safe coding that will take care of this issue.)



<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_Change()<br>TextBox1.Text = ""<br><SPAN style="color:#00007F">Dim</SPAN> rfound <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheet1<br>        <SPAN style="color:#00007F">Set</SPAN> rfound = .Columns(1).Find(What:=ComboBox1.Value, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _<br>            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _<br>            , SearchFormat:=False)<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">If</SPAN> rfound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    Application.Goto rfound, <SPAN style="color:#00007F">True</SPAN><br>    TextBox1.Text = rfound.Offset(0, 1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[BungleNZ]

Bump - help anyone?

 

[RoryA]

If you made the combo into a two column combo, you could hide the second column and then populate that with the contact numbers as you populate the names. You can then populate the textbox directly from the combo?

 

[BungleNZ]

Hi Rory,

The only problem there is that there are about 15 (and potentially more) textboxes that will be populated based on the contact name in the combobox.

I'm not that flash with vba (thanks again for the previous help), but I thought an easy way of achieving this would be to return the cell address of the combobox1.value, assign this cell address to a variable, and then just return the offset value of the variable into the textbox. Does that make any sense?

Thanks

Jamie

 

[RoryA]

Yes that makes sense. If you make your combobox a two column combo, then alter my previous code to this:

Private Sub FillContactList(strCustomer As String)
   Dim Namelist, ContList, varItem
   Dim lngIndex As Long, lngRow As Long
   Dim objDic As Object
   Me.ComboBox1.Clear
   With Sheets("Names").Range("Customers")
      ' get start row
      lngRow = .Row
      Namelist = .Value
      ' Contacts in col G, 6 cols to right of customers
      ContList = .Offset(, 6).Value
   End With
   Set objDic = CreateObject("Scripting.Dictionary")
   With objDic
      .Comparemode = vbTextCompare
      ' Loop through customer array (which is 2-dimensional as assigned from worksheet range)
      For lngIndex = LBound(Namelist, 1) To UBound(Namelist, 1)
         If StrComp(Namelist(lngIndex, 1), strCustomer, vbTextCompare) = 0 Then _
                                    .Item(lngIndex) = ContList(lngIndex, 1)
      Next lngIndex
      If .Count > 0 Then
         For Each varItem In .keys
            With Me.ComboBox1
               ' fill combo
               ' add contact name
               .AddItem objDic(varItem)
               ' add row number where contact found
               .Column(1, .ListCount - 1) = varItem + lngRow - 1
               ' select first item
            End With
         Next varItem
         Me.ComboBox1.ListIndex = 0
         With Me.SpinButton1
            ' reset min and max values for spinbutton
            .Min = 0
            .Max = Me.ComboBox1.ListCount - 1
            ' set spinbutton to first item
            .Value = 0
         End With
      End If
   End With
End Sub

you can then use:

Me.Combobox1.Column(1)

to get the row number stored in the second column of the combobox for the currently selected item. You can then use this with a column number in a Cells(row, Column) statement to get whatever value you need. Does that make sense?

 

[BungleNZ]

Excellent, thanks for that Rory - I've come up with the following combobox change code which seems to work exactly as required.

Many thanks

Jamie



<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_Change()<br><br>Primaryphone.Text = "" <SPAN style="color:#007F00">' Clears textbox</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> pphone$<br>    pphone = ComboBox1.Value <SPAN style="color:#007F00">' Checks to make sure there is a contact name in the combobox</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> pphone = "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">' Exits sub if no contact name in combobox</SPAN><br>    Primaryphone.Text = Cells(Me.ComboBox1.Column(1), 9)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>