UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA That Pulls From A List, Office 2013    
 
   
Dexter
post Nov 16 2017, 11:41 AM
Post#1



Posts: 625
Joined: 5-November 07



I have two tabs in the excel file that I am working in. The first one is for data entry. The second tab is a list of companies and the columns are name, phone and email. From the first tab, I want to be able to choose a company name from the second tab and have the corresponding phone and email to fill into specific cells on the first tab. Since the list of companies will most likely change over time, I want the list of companies to choose from to change with it. I know I can use data validation to get the names but then how do I get the corresponding phone numbers and email to copy over to my first tab? Thanks
Go to the top of the page
 
doctor9
post Nov 16 2017, 01:30 PM
Post#2


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Dexter,

Here's what I do in a similar situation (No VBA required, BTW):

First, add a new fourth column to your list of companies, with the heading ID, and place this column just to the left of the column with the company names. Fill in the column with a sequential list of numbers with no repeats. For example, put 1, 2, and 3 in the first three rows, then select these cells, and drag the lower right corner down to the bottom of the list of the companies so Excel auto-fills the rest of the ID values in that column. This will give your list of companies a "Primary Key" like in a database that you can use later.

Next, select all of the cells containing company data in the four adjacent columns, and name the range "CompanyList".

Next, add an ActiveX Control Combobox to your data entry worksheet. Have it cover at least one cell entirely.

Combobox properties:
Column Count: 2
Column Widths: 0 pt;100 pt
LinkedCell: B3 (actually the cell that the combobox is covering up).

Okay, so the upshot of this is: When the user selects the third company on the list, the number 3 is stored in the cell that's covered up by the combobox. I bet you can see what's going to happen next.

In the cell where you want the auto-filled-in phone number, put a formula along these lines: =VLOOKUP(B3,CompanyList,3) In other words, use the number that the combobox put into the covered-up cell to look up the value in the third column of the CompanyList range. To display the E-Mail address from the range, change the 3 to a 4. This works because the numbers are sorted in numeric order, which is a requirement for VLookup to work.

Since you are using a named range for your companies, you should be able to insert a row or delete a row and the range will automatically adjust. Personally, I like to store this list in a separate worksheet called "Lists", and then I hide the worksheet from the user. I've attached a simple demo.

Hope this helps,

Dennis
Attached File(s)
Attached File  ComboboxDemo.zip ( 11.78K )Number of downloads: 1
 

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Dexter
post Nov 16 2017, 03:58 PM
Post#3



Posts: 625
Joined: 5-November 07



Thanks for the reply. I think I did everything but the combo box is blank when I click on the down arrow. It expands, however, there is no data there to pick. What did I do wrong? Thanks!
Go to the top of the page
 
doctor9
post Nov 16 2017, 04:02 PM
Post#4


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Dexter,

I think I must have omitted this combobox property in my haste:

ListFillRange: CompanyList

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Dexter
post Nov 16 2017, 04:10 PM
Post#5



Posts: 625
Joined: 5-November 07



Very cool! Thanks - This is perfect! I appreciate your help as always.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 08:56 AM