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:
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

S
  • Question Question
Combo box help
Replies
7
Views
397
Micron
M
N
  • Question Question
EXCEL VBA COMBO BOX/DROP DOWN LIST
Replies
1
Views
452
Peter_SSs
Peter_SSs
J
  • Question Question
Drop Down that can have data pasted into it without losing the drop down
Replies
1
Views
141
Rijnsent
Rijnsent
R
  • Question Question
Data Validation with Keyword search
Replies
9
Views
221
Cubist
Cubist
D
  • Question Question
Data Validation List without duplicates
2 3 4
Replies
30
Views
993
kvsrinivasamurthy
K

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

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