Combo Box VLookup Questions

jim0352

New Member
Joined
Nov 30, 2003
Messages
17
Hello,

Last month I created a userform to use during my contract work. With the help of this board the form is up and running, but the more I read the board the more new things I would like to add. My latest venture is using combo boxes to enter info into my userform and to check if I've previously entered the info. I've read through quite a few previous posts and have come up with the following (thanks to all those who provided the examples in past postings related to combo boxes).

Specifically, I have a column of names in "B" and in column "K" I have an address. Combo Box 1 contains the following in the Row Source:MyRange; which is defined as:
=OFFSET(Contacts!$B$2,0,0,COUNTA(Contacts!$B:$B),1)

This should allow the combo box to grow as more names are added to the range.

I am using a command button to enter the combo box value into my worksheet. The applicable code is:

Sheets("Contacts").Range("B65536").End(xlUp).Offset(1, 0) = ComboBox1.Value

These two items work as I want them to work. I can type new names into the combo box on the userform and when I click the command button it places the value in the correct cell on the worksheet. When I open the userform again, the combo box is updated with the latest entries.

The problems start when I enter a value into the combo box, I want to know if I have previously entered it (which is easily done with match entry/match complete) and if I have entered it to display the address in a text box. I am using the following to accomplish this:

TextBox1.Value = Application.VLookup(Me.ComboBox1.Value, Sheets("Contacts").Range("B2:K50"), 10, 0)

This works as long as the name is already in the combo box. If I try and enter in a new name, I get an error and it highlights the above line of code. I am sure that I need to include some sort of line that says disregard this rule if the name is a new one. Any thoughts on how I would go about doing this?

Thanks for the help,
Jim
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Would be easier to address if you posted your code section dealing with checking for an existing name but a thought:

Code:
   Dim n
    If ComboBox1.Text = "" Then
        MsgBox "Enter a valid name", vbCritical, "USER ENTRY ERROR"
        Exit Sub
    End If
    'check for duplicate name
    n = ComboBox1.Text
    If UCase(n) = UCase(Sheets("Contacts").Cells.Find(What:=n, LookIn:=xlFormulas, _
                        LookAt:=1, SearchDirection:=xlNext, MatchCase:=False)) Then
               TextBox1.Value = Application.VLookup(n, Sheets("Contacts").Range("B2:K50"), 10, 0) 

Else
      'adding a new name and whatever code you want here
    End If

HTH
 
Upvote 0
Thanks for the response. Right now, I don't have any code other than:

TextBox1.Value = Application.VLookup(Me.ComboBox1.Value, Sheets("Contacts").Range("B2:K50"), 10, 0)

that checks for existing names. As I type a name in the combo box, using the match complete in the combo box properties, I can tell if the name has been previously entered. Using the above, if the name is already in the combo box, it displays the address in a text box. The problem arises when the name is not already in the combo box then the VLookup doesn't work and I get the error because it is looking for something that is not there. I guess what I'm trying to say is if the value I enter already exists in the combo box, I want to do the VLookup. If it doesn't exist, I don't want to do the VLookup.

Hopefully I've explained myself better and I'll also give Trying to Learn's code a try.

Thanks,
Jim
 
Upvote 0
I can't seem to get TryingtoLearn's code to work with my current setup. Any other thoughts. Hopefully my last post provides more insight as to what I am trying to accomplish.

Thanks,
Jim
 
Upvote 0

Forum statistics

Threads
1,206,814
Messages
6,075,021
Members
446,114
Latest member
FadDak

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
Back
Top