User Form with with Populating Combo Boxes

**Maneesh Massey** · 11-22-2013, 02:04 PM

Hello,

I am working on a very large spreadsheet of data in Excel 2013 Pro Plus edition. I need to have the following functionality to quickly find and bring it to display in the table I am working with. This table is HUGE! Having over 25,000 records of agent ID's, names of Agents, their addresses, phone, email, and the products they sell in their respective districts and sub-districts.

I would like to make use of a simple Userform and have something like this:

-------------------------------------------------------------------------------------------
PID: Text Box
District: Combo Box
Sub-District: Combo Box

RESET BUTTON SHOW DATA BUTTON

TABLE HERE

-------------------------------------------------------------------------------------------

The above is the best I can visualize here. But this is a synopsis of what functionality I am looking for.

The First Text Box will be the PID Text Box, where the user can enter a PID (an agent ID code) and presses the SHOW DATA button, which then displays all relevant information like name of agent, address and other details in the table below. It will essentially work like a search tool.
If the user does not know the PID, he/she can make a search using the District and Sub-District Combo Boxes. The user makes use of the District Combo Box and selects a district and the Sub-District Combo Box automatically updates and loads the correct Sub-Districts corresponding to the District chosen. The user selects a Sub-District from the Sub-District Combo Box and presses the SHOW DATA Button which then displays all the Agents belonging to the District and Sub-District with the details of all the agents working in the particular chosen district. This is basically how this system should work.

Where do I start with this ?
Any help greatly appreciated !
~~ Maneesh

**apo** · 11-22-2013, 03:55 PM

Hi..

Depending on how your data is structured.. if it is currently (or you can create a "Data" sheet) like the following:

District	Sub District
District 1	Sub District A
	Sub District B
District 2	Sub District C
	Sub District D
District 3	Sub District E
	Sub District F

Then you could use Jindons code from here quite easily..
http://www.excelforum.com/excel-prog...-userform.html

Then.. you could just autofilter your main sheet by the selection in the "District" and/or "Sub District" combo and display the results in a big Listbox on the Userform..

If you are still stuck.. best bet would be to attach a sample workbook (anonymised) showing the structure of your data..

**Maneesh Massey** · 11-23-2013, 04:46 AM

Hi Apo,

Thanks for the reply. And thanks for the example you gave for the combo boxes. It is exactly what I was looking for. There are a few
other things I am stuck on. I need help. I have attached a file called "UserFormTest" with this reply. Please have a look at the file and Form I made by going to the Visual Basic Editor. All data used in the file is completely fictitious.

The UserForm should have a the following functionality:

User enters PID (AgentID code) into the PID textbox and presses the Find button next to it. The agent's complete details like his name, address, phone, zone, region etc. etc. should be displayed in the Excel worksheet behind the form.

If the user does not know the PID, then he/she selects a District from the district combo box which automatically updates and populates the Sub-District combo box with the sub-districts corresponding to the district chosen. The user selects a sub-district to go into and presses the "Show Data" button which should display all agent information pertaining to the sub-district chosen. The user can make appropriate changes in the Excel sheet behind the form and save the sheet.

The Update Drop Downs button makes ready all the worksheet data, and combo boxes in memory before someone selects an item from the combo boxes. Essentially loads all items in memory but does not display them. I can also get rid of this button if it is a hassle.

The Reset button clears up the text box, combo boxes and the Excel sheet if it is displayed in the background.

The Add New Record button should open another form, which is simply a data entry form in which the user enters data for a New Agent and other details belonging to him/her. Once the Save button is pressed, the second form closes and first form is displayed as a modal form. If the user clicks the cancel button on the second form, control should go back to the first form.

This is basically all I want the application to do.
Can you please help me set this up ? Once I learn how to program this application, I can take things from here and make some more applications.
Appreciate all your help!
~~ Maneesh