data validation/combo box for long list of items

L

Leslon

New Member
Joined
Jan 18, 2010
Messages
1
Hello,
I've been searching the web and this forum and I still haven't found what I'm looking for. Here is my situation:

I am making a client tracking spreadsheet that needs to have exact client names in order to link to other spreadsheets. The first thing that came to mind was to use data validation. The only problem is that we have 500 clients and scrolling down is a pain considering I have to use this cell about 500 times. Contrary to some posts I've read, I am not able to type in a letter which will jump me down the data validation list. Also, our client names are far to long and complicated for AutoComplete to fill them in so with data validation, it seems I have to type the whole name exactly correct or use the 500 name scroll down.

So then I read up on combo boxes which will narrow down your choices as you type. That's exactly what I need and it would save me a ton of time. What I don't like about these, however, is that they float as objects. I need them to be in a cell so I can drag copy it to other cells. Other information on this spreadsheet will be exported based on this client name so it's very important that it read like a regular cell. For instance, the client name might be used in a VLOOKUP function.

Is there a way to modify a cell to act like a combo box or embed the combo box into a cell? Is there another way to achieve my goal that I am overlooking?

http://www.mrexcel.com/archive/Data/13157.html I ran across this post and I want whatever Paul E. is talking about. Hope it helps.

I am using Excel 2007.

Thanks in advance,
Leslon
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
I think you might benefit by using a userform, here is some code from one of my userforms I use to enter data into a table I use as a ledger:

Code: 
'cmdAdd is a command button
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet

Set ws = Worksheets("Checking")


'find first empty row in database
'This is used to determine if this is the first entry in the table or not
'Check box is selected manually for the first entry, left unchecked for every
'entry after the first
If cbxFirst.Value = "True" Then
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Row
Else
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
End If

'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtDate.Value
ws.Cells(iRow, 3).Value = Me.cbVenue.Value
ws.Cells(iRow, 4).Value = Me.txtDesc.Value
ws.Cells(iRow, 5).Value = Me.txtType.Value
ws.Cells(iRow, 6).Value = Me.txtDepo.Value
ws.Cells(iRow, 7).Value = Me.txtWith.Value
ws.Cells(iRow, 9).Value = Me.txtVeri.Value

'clear the data from userform
Me.txtDate.Value = ""
Me.cbVenue.Value = ""
Me.cbDesc.Value = ""
Me.cbType.Value = ""
Me.txtDepo.Value = ""
Me.txtWith.Value = ""
Me.cbVeri.Value = ""

'Workbook Refresh
ActiveWorkbook.RefreshAll

'Refresh Venue to see new data entries
frmMain.cbVenue.RowSource = "Venue"

Venue is a named range I created based on the Venue column in my table.

This will obviously need tweaking for your needs.

Here are some links if you need guidance for making user forms:

http://www.contextures.com/xlUserForm01.html

http://msdn.microsoft.com/en-us/library/bb149075.aspx

If this isn't what you're looking for I apologize, this is how I prefer to do data entry over data validation lists and putting multitudes of objects on a worksheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Populating combo box based on which cells are empty
Replies
3
Views
162
Anthony47
A
N
  • Question
Macro to Clearing Contents of Row affect Validation Lists
Replies
0
Views
200
Nathan Asius
N
J
  • Solved
Data Validation List - Not Including Blanks
2
Replies
12
Views
286
kevin9999
K
A
  • Question
Show Data Validation Lists in Next Row of Table
Replies
0
Views
398
AndyPandy27
A
N
  • Question
Multiple Conditional Data Validations
2
Replies
13
Views
671
Nathan Asius
N

Forum statistics

Threads
1,214,593
Messages
6,120,435
Members
448,962
Latest member
Fenes

Share this page

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