Data table function

M

maestro

I have a data table with text fields and associated values. In another part
of the spreadsheet I need for Excel to match a text entry (when I type it) to
the table and copy the associated value to another cell in the worksheet. Is
this possible?
 
S

SongBear

maestro
Generally speaking, yes there are a couple of ways to go.
For instance if your table is one column of text lables and one column of
values, then you would use a VLookup function.
There is a very good explaination of VLOOKUP in Excel help, along with other
table lookup functions.
Example:
the formula in cell I5 is: =VLOOKUP(B2,B4:C13,2)

B C
5 text6 753
6
7 text1 122
8 text2 211
9 text3 321
10 text4 123
11 text5 456
12 text6 753
13 text7 159
14 text8 951
15 text9 357
16 text10 852

Please let us know if this answers your question.
If you need more help or clarification, let us know.
SongBear
 
M

maestro

I think I did a poor job of explaining myself. My apologies. I plan to have
a table like you described with a column of text and a column of associated
prices.
Example:

A B
blue widget $15.95

These columns will contain dozens of rows. When I type "blue widget"
somewhere else on the worksheet, I want it to bring up the associated price
and drop it into another cell.

Does this make sense?

Regardless, thank you very much for responding so quickly.

Maestro
 
R

Roger Govier

Hi

Assuming the cell you enter Blue widget is D2 and you want the price
returned in E2.
In cell E2
=VLOOKUP(D2,$A$B,2,0)

If you wanted to do in on another sheet, say Sheet2, but with your
lookup table on sheet1, then
=VLOOKUP(D2,Sheet1!$A$B,2,0)
 
S

SongBear

maestro
What you described sounds like what I provided, i think. You would put that
formula anywhere on the spreadsheet. You would have that type in cell
anywhere, as well.
If you build the example as I presented it in a clean sheet, you will
understand what I am talking about. The fact that I used different words...OK
i just checked something, you need to us this formula if the list of text is
not sorted alphabetically.
VLOOKUP has 4 terms, the last one must be false for unsorted lists.
=VLOOKUP(B2,B4:C13,2,FALSE)
For the first term, in the example B2, put in the address of the cell where
you will be typing in the text, place the formula where you want the number
to appear.
The second term (after the first coma) is the table address.
The third term (after the second coma) is the column in the table to return
a value from.
The fourth term, is False for un-sorted lists where you need an exact match,
it will return #NA if you spell the lookup item wrong, but you can provide
dropdown list functionality to overcome that.
Lets build a more complex example.
Lets add a third column and a fourth column to the table, the third will be
a mystery for this example, it is there for a place holder and could be
anything, whatever is in it, we don't need or care...but the all important
fourth column is were we find the shipping weight.

Here is the small example table:
J K L M N
2 ITEM COST Desc. SHIIPPING
3 Free sample $- Yadda0 0.75
4 text1 $12.56 yadda1 1.22
5 text2 $18.44 yadda2 2.11
6 Green Widget $0.33 yadda3 3.21
7 Blue Widget $15.95 This... 1.23
8 NotA Widget $1.98 yadda5 4.56
9 Yellow Widget $6.01 yadda6 7.53
10 text7 $8.23 yadda7 1.59
11 text8 $4.44 yadda8 9.51
12 text9 $300.00 yadda9 3.57
13 text10 $6.75 yadda10 8.52

15 Shipping: 0.12

Note that the table is built in columns K through N and the data is in rows
3 througn 13. Yours can have more rows and even columns. Column J is used to
orient you in the example. Now lets build an order sheet.
For the example I added a 'shipping factor' in L15, it could have been
anywhere.
Back in Column B, I set up a lookup cell to get the description of any item
(I decided the description is in that second column) The lookkup formula goes
in cell C4 and it references cell B4 where you type in the item name; the
formula in C4 is:
=VLOOKUP(B4,K3:N13,3,FALSE)
Note that i referenced the third column in the list as the third term in the
formula. On the spread sheet it looks like this:

Get Item Description Here:
Blue Widget This is a full description of this item.


The words "This is a full description of this item." are in the third column
of the list for the Blue Widget. It is not all vislible in the list because
the column is too short, but the text is there. It becomes visible in the
lookup box.

NEXT: below the description lookkup, we build an ordersheet.

Starting in row 10 with headers, and continuing below that with price and
shipping lookups and calculations...
In operation, it will look like this:
Select Order Here: Cost Shipping
Yellow Widget $6.01 $0.90
NotA Widget $1.98 $0.55
Text7 $8.23 $0.19
text2 $18.44 $0.25
Green widget $0.33 $0.39
text1 $12.56 $0.15

Totals $47.55 $2.43
Order Total $49.98

I just entered the lookup function twice, once in the first order cost cell
(Next to where the Yellow Widget is being ordered) and once in the shhipping
cost box in the next cell over. I then copied down the two formulas to the
next few cells below...
I will show the cost column first. This message tool is too narrow to show
both at once.

Select Order Here: Cost
Yellow Widget =VLOOKUP($B11,$K$3:$N$13,2,FALSE)
NotA Widget =VLOOKUP($B12,$K$3:$N$13,2,FALSE)
Text7 =VLOOKUP($B13,$K$3:$N$13,2,FALSE)
text2 =VLOOKUP($B14,$K$3:$N$13,2,FALSE)
Green widget =VLOOKUP($B15,$K$3:$N$13,2,FALSE)
text1 =VLOOKUP($B16,$K$3:$N$13,2,FALSE)

Totals =SUM(C11:C17)
Order Total

The next column over is set up like this...
Shipping
=VLOOKUP($B11,$K$3:$N$13,4,FALSE)*$L$15
=VLOOKUP($B12,$K$3:$N$13,4,FALSE)*$L$15
=VLOOKUP($B13,$K$3:$N$13,4,FALSE)*$L$15
=VLOOKUP($B14,$K$3:$N$13,4,FALSE)*$L$15
=VLOOKUP($B15,$K$3:$N$13,4,FALSE)*$L$15
=VLOOKUP($B16,$K$3:$N$13,4,FALSE)*$L$15

=SUM(D11:D17)
=C18+D18

If this is not the kind of thing you are describing, I would need more
explaination of what you are trying to do. It sounds like, from your
description, that you want to do what is accomplished in the 'cost' column
above. Is that not it?

NOTE: This Microsoft article may be helpful:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx?pid=CL100570551033

I found this article personally helpful about a week or so ago:

http://office.microsoft.com/en-us/excel/HA011549021033.aspx?pid=CL100570551033

Also try:

http://office.microsoft.com/en-us/excel/HA010563201033.aspx?pid=CL100570551033

Please let us know if this helps, if not, maybe you can give us a more
detailed description of what is different about your application so that I
can see better how to help.

SongBear
 
S

SongBear

Hi Bruge
For one thing, If you use the 0 at the end of your formula, the match to the
list has to be exact. This means that your spelling, spaces and such in your
lookup value in B4 have to be exactly like what is in the list to find a
match in the list. If you use a 1 at the end of the vlookup formula, it will
find a "close enough" match...which can be inaccurate much of the time, so my
advice: stick with the 0 but find a way to get an exact match. A way is
discussed below, but your list is 18,000 long and that may make it messy.
here is what I have so far...
I created a sheet to test this and filled it with random yucky. I named the
sheet Heights Data.

ColA ColB ColC ColD ColE ColF
Town name County Zip Latitude Longitude Height (above Sea Level)
houstosn haris 71000 1.2 2.1 100
spring isar 72000 2.3 3.2 200
conroe aris 73000 3.4 4.3 300
tombal buggy 74000 4.5 5.4 400


I then created a dropdown list using the Data|Validation tool as follows:

I selected my town names in column A on the 'Heights Data' worksheet and
created a range name: "Town_Names".
(how to create range names is in the help menu, if needed. Let me know if
you need help with that.)

I then went to a new sheet, selected B4 and went to Validation on the Data
menu.
In the Validation dialog box, i selected the settings tab, then "List" under
Allow:, and typed in "=Town_Names" (no quote marks) in the Source: box. Then
OK.

(Further instructions on how to do this: in the Excel help box, type:
"dropdown list"
then select the topic:
"Create a drop-down list from a range of cells"

What this gained me was a dropdown box in B4 on the calculation sheet with
names exactly as they appeared in the data table.

I then created these five formulas in the next five cells to the right:
C4 =VLOOKUP($B$4,'Heights Data'!$A$3:$F$6,C3,0)
D4 =VLOOKUP($B$4,'Heights Data'!$A$3:$F$6,D3,0)
E4 =VLOOKUP($B$4,'Heights Data'!$A$3:$F$6,E3,0)
F4 =VLOOKUP($B$4,'Heights Data'!$A$3:$F$6,F3,0)
G4 =VLOOKUP($B$4,'Heights Data'!$A$3:$F$6,G3,0)

Note that I used the cells above the formulas (C3 to G3) to contain the
column numbers that I wanted to retrieve. In this case I skipped the Town
Name column as it was already in B4 and placed 2 through 6 consecutively in
the cells above.
I then used the drop down list in B4 to select town names. Typical results
are as follows, compare with my original list:

B C D E F G
3 2 3 4 5 6
4 spring isar 72000 2.3 3.2 200

B C D E F G
3 2 3 4 5 6
4 conroe aris 73000 3.4 4.3 300

B C D E F G
3 2 3 4 5 6
4 houstosn haris 71000 1.2 2.1 100

If I typed in a name in B4 (before I created the dropdown list), and it was
not exact, I got #NA in the lookup formulas, just as you did, so the probable
problem was in not having exact matches in the Heights Data town name list.
In some cases there are hidden problems with imported data, such as spaces in
front of each name in the Town Names list, in which case, if you didn't
discover it, you would NEVER get a match. Frustrated forever.
Hope this helps, please let us know if it helped or if any of this needs
further clarification.
SongBear
 
B

Bruge

I have read with interest the content of this discussion since it seems to be
the answer to my problems.
I have a spreadsheet containing approv 18 000 records of data relating to
latitude etc for all Towns, Villages etc and wish to design a search facilty
on a separate worksheet which will allow me to type in a particular Town name
and then present that row of information on the sheet.
From that information I can then apply a special calculation based on the
information from two or three cells.
I have been trying to use VLOOKUP but keep getting #N/A despite many
attempts, trials etc.

the basic layout is :

ColA ColB ColC ColD ColE ColF
Town name County Zip Latitude Longitude Height (above Sea Level)

my formula is: VLOOKUP(B4,A2:F18000,1,0)

Can anyone offer help please?
 
B

Bruge

*see* I am going barmy now - the reference B4 in my formula is the type in
box on Sheet 1 and the references A2:F18000 are the fields on sheet 2, actual
formula is

VLOOKUP(B4,'Heights Data'!A2:F18000,1,0)
 
B

Bruge

Hi SongBear

Thanks for that - it works well now - pity I cannot just type in a town etc
- would make it a bit quicker.
Probably now look at the data sheet to see if I can clean it up a bit.
Thanks again
 
B

Bruge

Hi again.
Have now cleaned up the Town Names column using TRIM() and VLOOKUP works
well BUT as you can imagine there are some Towns etc with multiple entries
but at very different Latitude positions so it would be nice to have a
facility where I can type in a Town Name and have a browse window open
listing that choice plus the next 10 or so entries downwards so that the
correct Latitude can be chosen.
 
S

SongBear

Bruge
One thing that I should have mentioned. Vlookup only selects the first
match. If you put a town name into the box and vlookup returns a set of
values from the columns to the right of the lookup column, the values are
going to only be for the first town with the same name. I downloaded census
data with town and state names to test what was happening with a large list
containing duplicate town names. I knew that Vlookup behaved like this but
wasn’t sure what was in your list.

For instance, there are (in different states) five Abbeville’s, five
Aberdeen’s, and seven Adams’s. I created a dropdown box to select cities,
selected the fourth Abbeville (South Carolina) and the Vlookup formula
returned data from only the first Abbeville (Alabama). I could select the
different Abbeville’s as many times as I wanted, the VLookup cell to the
right did not change from the value associated with the first Abbeville in
the list.

For this reason, if you have duplicate values in the Town Names, I believe
you will need to replace Vlookup with something else. I am working on a more
reliable solution using a form and VBA code - which will give you the popup
utility that you mentioned – and I will post it when finished. Since we
cannot post sample work sheet here, I have to figure out adequate
instructions on how to do it. It would be handy to know if you have any
familiarity with VBA or MS Forms so that I would know where to start..

If you are still typing town names into the calculation worksheet then I
take it you are not using the dropdown list available under Data|validation
to select the towns. I tested it with the over 25,000 place names that I
downloaded from the Census bureau (to test solutions against, lacking your
list for that), and it gave acceptable performance, response time wise.
Unfortunately, that tool will only allow one column, inadequate for a list
with duplicate names.

Meanwhile, as a potential function-only solution to viewing multiple matches
using VLookup, see this article:
http://office.microsoft.com/en-us/excel/HA012260381033.aspx?pid=CL100570551033


Question: did you use the trim() function in VBA code to clean up the list,
or are you somehow using it inside of VLookup? Please post how you did that
so other people can benefit from your experience.

SongBear
 
B

Bruge

Hi songbear
Thanks for the quick response. I have programmed using VB5 albeit on small
programmes only and have limited experience with VBA. I used the TRIM
function courtesy of an article in Worksheet Functions - "Remove trailing
spaces from multiple columns in Excel" - posted 3/14/2006.
Basically I created a new empty column B and inserted TRIM(A1) into the new
B1 cell. I then used copy / paste to insert this formula into the remaining
17999 cells. Finally I used Copy to all the new B cells and Paste Special
Values back into Column A.
Hope this is of use.
 
B

Bruge

Sorry Songbear
I omitted to respond to your observation that I am not using the
Data/Validation since I managed to "clean up" the Town Name column as I still
prefered to Type in a name - even the Data/Validation procedure will only
allow viewing of one Town etc. What would be perfect is the ability to select
from a Type in box and display say 10 lines showing the first 4 columns of
data i.e. Town Name, County/State, Postcode/Zip and Latitude, this way I can
identify the correct Town to choose based on its respective Latitude, and
thus select that Row from this "Browse" window.
 
S

SongBear

Bruge,
You will want to replace VLookup with something that will allow you to
access a table with duplicates in the first column. VLookup will only find
the first instance of duplicates even if you select the second of the same
names to put into the Town box. And you are typing-in and not selecting from
a list, anyway. Below is the complete solution to this problem,

To do this you would be well served by a UserForm which we will create now.

Open the workbook with the list and the selection/calculation worksheets
and, if you have not already, on the worksheet where you are typing in the
town name and using the VLookup formulas, select the single cell B4 (or the
cell where you put the town name; the data cells containing your VLookup
formulas should be just to the right, if I understand you correctly), and
create a range name for that cell, I called B4 on my lookup/calculate
worksheet: “DataTargetâ€. You will see why it is important to have this cell
named later.

On the data sheet, select the headers of the table of town names and info
and shift/control/down to select the table, then give the entire table a
range name; I called mine ‘Pop_Data2’ because I downloaded a list of towns
and populations from the US census web cite to use to build and test this.

Open the VBA Editor (Tools|Macro|Visual Basic Editor). In the VBA
programming environment, click Insert|UserForm. When the UserForm is showing,
click on the new UserForm and look around the VBA window for a floating
toolbox, called, oddly enough, Toolbox. If this is not showing, go to the
View menu and select Toolbox.

In the toolbox, ‘Toolbox’, move and hold your mouse cursor (unclicked) over
each button until the tooltip shows that you have found the ComboBox button.
Click to activate and draw a wide horizontal rectangle on your new UserForm.
Start with approximately the full width of the UserForm and about ½ inch
high. This will create a ComboBox control on the form. A ComboBox control is
a dropdown list that allows you to make selections from the list.

If it is not already showing, bring up the Properties window (in the VBA
environment, the button for Properties is the small hand holding a sheet of
paper just as in other Microsoft offerings).

The top of the VBA properties window has a ComboBox that enables you to
select what object to show properties for. So far, you have two objects, a
UserForm and a ComboBox.

With the properties showing, the name properties of your two objects are
available for you to modify. The name property of the ComboBox appears in the
Appearance category; for the UserForm, it is under the "Misc" category.

You should name both as you will be using these names later. I named my
UserForm “TownSelector†and my ComboBox “TownListâ€. NOTE: it is customary and
good practice to use a naming convention, such as frmTownSelector and
cboTownList. If you develop good habits now (unlike me), you will be glad
later as you work on larger projects.

Next. add three CommandButtons to the UserForm, using the same method as for
the CombBox to find and use the CommandButton control in the toolbox,
‘Toolbox’.

Using the Properties window, name the buttons. Mine are “Clear_Formâ€,
“Exit_Buttonâ€, and “PostChoiceâ€. Clearly, no naming conventions were used
here. You might use “btnClearFormâ€, “btnExitâ€, and “btnPostChoiceâ€, or some
such.

Find the Caption property for the buttons and change the “PostChoiceâ€
button’s caption to “Send Choice to Worksheetâ€, the “ClearForm†button’s
caption to “Clear Selectionâ€, and the “Exit_Button†button’s caption to
“Close Selection Boxâ€. Use your button names, of course.

Note that you can also change the color, font and other properties of these
buttons, at least in Excel 2003 and possibly in earlier versions of Excel.
The UserForm can also be made a more eye-pleasing color than gray, if you
wish.

If you have not had to already, you may have to resize the UserForm to
accommodate the next additions. Using the toolbox, ‘Toolbox’, add two
Labels. Resize the two labels to fit the following text: (Label1 is the title
label and should have a large font.)

Contents of the two labels:
Label1: “Selection Boxâ€
Label2:
“Typing in the selection box will bring matching choices, shortening the
path to your selection during dropdown. Use the Clear Selection button to
clear the selection box of the previous selection and enable typing in
letters for a new match.

Send your choice to the worksheet with the send button, you can move this
dialog out of the way and keep trying selections and viewing the result
without closing this selection dialog box. When you have made your final
selection, use the close button to close this form.â€

You can get the linefeed in the Label control by hitting shift and enter at
the same time. (You might be able to paste it in if you use what I typed.)

Next we finally get serious. We start to Code. Right click on the
“PostChoice†CommandButton that you placed on you UserForm and select ‘View
Code’ in the dropdown menu. This will take you to a code window with the
cursor in an already prepared but empty Subroutine called “Private Sub
PostChoice_Click()†or rather, “Private Sub [YourButtonName]_Click()†. In
this sub, (above the End Sub line and below the Private Sub line), post the
following code (without the stars).

‘***********************
Dim intNDX As Integer
If TownList.ListIndex + 1 >= 1 Then
intNDX = TownList.ListIndex + 1
Else
intNDX = 1
End If
With ActiveSheet
rngTarget.Select
rngTarget.Value = aryTowns(intNDX, 1)
Selection.Offset(0, 1).Value = aryTowns(intNDX, 2)
Selection.Offset(0, 2).Value = aryTowns(intNDX, 3)
Selection.Offset(0, 3).Value = aryTowns(intNDX, 4)
Selection.Offset(0, 4).Value = aryTowns(intNDX, 5)
Selection.Offset(0, 5).Value = aryTowns(intNDX, 6)
End With
‘**********************
Note that the intNDX is an integer used to temporarily store the index
number of your selection for later use. Also note the use of “TownListâ€,
which is, if you recall, the name of my ComboBox. You will need to change
this word to whatever you named your ComboBox, leave all else the same.
ActiveSheet is the worksheet that you will call this form from, the selection
and calculation worksheet. We are going to Declare rngTarget next and assign
it a value later.
This code will take the data belonging to the town that you select and paste
that data into the five cells to the right of B4, your named range where you
put the Town name. As this will replace the current contents, which are your
VLookup formulas, you might want to copy these formulas and paste them into a
Notepad text file to save them in case you need them for something else. I am
taking this data from the Array, which still exists even though I assigned
its values to the ComboBox list. It is easier to extract multi-column data
from an Array than a ComboBox.
Next, go to the top of the code window (just above the button-click
subroutine and outside of and above any subroutine) and paste the following:
(not the stars)

‘***********
Option Base 1
Dim aryTowns() As Variant
Dim rngTarget As Range
‘***********
Here we have declared our Option Base to be 1, this means that the first
element in arrays will be numbered 1 instead of starting the numbering with
0. We then declared two variables, an Array, which is an in-memory list or
table of data, and an Object variable of the type Range. We can assign to an
Array data type an actual worksheet range, consisting of a single cell or a
multiple cell selection, and expose the properties of this Range to our
program. The properties that we are interested in are this range’s location
on the page, to guide us as to where you want to put the other data, and the
cell’s contents or value, which we wish to set.

At the top of the code window, above the Option and Dim declarations you
just pasted, are two ComboBoxes (dropdown lists). Since you just pasted the
declarations, the left one should have the word “(General)†in it and the
right one should have “(Declarations)â€.

Dropdown the left ComboBox at the top and select “UserFormâ€; then drop down
the right ComboBox and select “initializeâ€. You should get a new subroutine,
“Private Sub UserForm_Initialize()â€. This subroutine does not have to use the
UserForm’s name because it is private to this form and not visible outside of
this form. What will happen is that whatever code is in this subroutine will
run when and each time the form is first called up.

In this empty subroutine, between the “Private Sub UserForm_Initialize()â€
line and the “End Sub†line, paste this code: …(Not the stars)

‘**********
Set rngTarget = Range("DataTarget")
aryTowns = Range("Pop_Data2")
TownList.List = aryTowns
‘**************

Remember that I named my ‘B4’ (Town name) cell on the selection/calculation
page “DataTargetâ€. This code sets the range object variable ‘rngTarget’ to
that named range. (Object variables have to be ‘Set’, not just assigned with
a plain old equal sign, to something compatible to its type)
This code also assigns the values in the named Range, which I named
‘Pop_Data2’, that is the data table in the data worksheet, to my Array
variable ‘aryTowns’. Use your table’s range name to replace ‘Pop_Data2’ here.
Immediately after the code line assigning the table data in the named range
to the Array, the next line assigns that same Array to the ‘List’ property of
the ComboBox ‘TownList’. Replace ‘TownList’ with whatever you named your
ComboBox here.

Note that this will be the first code in the form to run, but since it is
event-triggered, it does not matter if it is not the first or top subroutine
in the code window, so you can leave it wherever it has landed.

Next, go back to the left side ComboBox at the top of the code window, and
pull down and select the name of your clear-form button. Select ‘Click’ in
the right hand ComboBox. You should get something like this:

Private Sub Clear_Form_Click()

End Sub

Inside of this, paste the following code:

‘********
TownList.ListIndex = 2
TownList.Text = ""
TownList.SetFocus
‘***********
When done, it should look something like this:

Private Sub Clear_Form_Click()
TownList.ListIndex = 2
TownList.Text = ""
TownList.SetFocus
End Sub

Substitute the name of your ComboBox here. This just routine clears the
ComboBox of previous selections so that you can more easily type into it;
typing a few letters of the town name speeds up the selection process greatly.

Next, go back to the built-in ComboBox at the top left of the code window
and select the ‘Exit’ button. (Mine is named ‘Exit_Button’, yours may be
named just ‘Exit’ or ‘btnExit’. You should get something like:

Private Sub Exit_Button_Click()

End Sub

This takes the single line below, substitute your UserForm name here.
********
Unload TownSelector
********
To give you:

Private Sub Exit_Button_Click()
Unload TownSelector
End Sub

This just closes the UserForm.

You should now have one set of declarations and four subroutines, one
subroutine for each control (the buttons and the dropdown box) on the
UserForm. There is no error control, it really is not necessary for a simple
task like this, unless you are going to distribute this as an application.
Leave that for the future, this is tested code, except for using your object
and range names in the correct places.

Next, you need to go back to the Object View and look at the UserForm again.
In the top of the VBA window which your code window is hosted by, Click
‘View|Object’ to bring the UserForm back into design mode. Your properties
box should still be showing, if not, call it back up. Now, click on the
ComboBox.

In the ComboBox properties, AutoWordSelect should be True, MatchEntry should
be 1 – fmMatchEntryComplete, ColumnCount should be 6, and ColumHeads true.
You may have to set ColumWidths to match your data, experimentation will
tell you how much. My columns are 300 pt; 150 pt; 100 pt; 90 pt; 80 pt; 100
pt. That may be wider than you need. Just type in the numbers that you want,
with comas, VBA will supply the ‘pt’ and the semi-colons. Experiment later
when it is all running to see if you need to adjust the column widths.

My ComboBox’s ControlSource property is ‘DataTarget’ (no quote marks), which
is the name of the cell B4 on the selection/lookup/calculation page. The
range name given to that cell can be just typed into this property in the
property box.
I set my ListRows property to 20, I do not like having just the default of 8
rows showing when I drop down the ComboBox.
As for the rest, you can adjust the font here; also note that you may need
to adjust the size and positioning of the ComboBox to accommodate the six
columns of data you need to show.

Other than messing around with control sizing and positioning, and form and
control formatting, you should be pretty much finished with the UserForm.

Leave the VBA environment open for now, you may have to go back to it to
adjust the ComboBox later. For now, switch windows to the Excel workbook,
specifically to the Selection/Lookup/Calculation worksheet.

You need to create a CommandButton on this worksheet to use to call up the
UserForm. With the lookup/calculation worksheet showing, Click View|ToolBars
and see that Control Toolbox is selected. Once that toolbar is showing, click
the ‘Design Mode’ button (it has a triangle, a ruler, and a pencil on it).

Once the worksheet is in Design Mode, go back to the same toolbar and click
the CommandButton control to add a CommandButton to your worksheet. Select a
convenient area that is visible and draw the button there (mine is placed
right under B4, the cell that is named DataTarget).

Go back to the same toolbar and select Properties, again this is the button
with a hand holding a sheet of paper. In the properties window, with the
worksheet still in design mode, select the CommandButton. In the
CommandButton properties, set the caption to “Update Town Data†or something
you will recognize. You might prefer something like “Change Townâ€.
In the ‘(Name)’ property, name the CommandButton; mine is named ‘UpdateData’
yours might be ‘btnUpdateData’. Adjust other properties to taste.

Now, right-click on the CommandButton. Select ‘View Code’. This should take
you to a new code window (this code window belongs and is private to this
worksheet.)

Only one Subroutine should be showing:


Private Sub UpdateData_Click()

End Sub


Yours should see the name you gave your button instead of “UpdateDataâ€.
Inside this empty subroutine, paste:

‘***********
TownSelector.Show
‘**********

The only Subroutine showing in this code window should now be this:

Private Sub UpdateData_Click()
TownSelector.Show
End Sub

You are finished here and you are back in the VBA environment, so switch
back to the Worksheet window and to the lookup/calculation worksheet where
the new CommandButton is.
Take the worksheet out of Design Mode. Hold your breath, cross your eyes and
fingers, stand on your head and hop on one foot all at the same time, just
for luck, then click the button. When the smoke clears and you get your new
computer installed, let me know what happened.
Really, it should work, barring missing a place or so where you need to
change an object name or a range name in the code. Let me know if you need
help troubleshooting it.

:Let us know if this helps or if you need further help or clarification. I
do not really believe that we will get this in one try; too many steps, but
keep updating me of where you are in the process and I should be able to
further explain where this is deficient.
SongBear
 
S

SongBear

Version 2
Bruge, I re-tested the town selection tool this afternoon and it was not
working the way it was last night. I have made a fix and will post an
upgraded copy of the instructions, use this set instead of the previious set.
I am still working on the re-write; I did proofread it last night, but I was
tired and missed a couple of things. So, besides the simple fix for what it
started to do wrong today, I am also proofreading and editing it again.
Almost finished and will post it in a bit.
SongBear
 
S

SongBear

CORRECTED COPY:

Bruge,
You will want to replace VLookup with something that will allow you to
Correctly access a table with duplicates in the first column. VLookup will
only find the first instance of duplicates even if you select the second of
the same names to put into the Town box. And you are typing-in and not
selecting from a list, anyway. Below is the complete solution to this problem,

To do this you would be well served by a UserForm which we will create now.

Open the workbook with the list and the selection/calculation worksheets
and, if you have not already, on the worksheet where you are typing in the
town name and using the VLookup formulas, select the single cell B4 (or the
cell where you put the town name; the data cells containing your VLookup
formulas should be just to the right, if I understand you correctly), and
create a range name for that cell, I called B4 on my lookup/calculate
worksheet: “DataTargetâ€. You will see why it is important to have this cell
named later.

On the data sheet, select the headers of the table of town names and info
and shift/control/down to select the table, then give the entire table a
range name; I called mine ‘Pop_Data2’ because I downloaded a list of towns
and populations from the US census web cite to use to build and test this.

Open the VBA Editor (Tools|Macro|Visual Basic Editor). In the VBA
programming environment, click Insert|UserForm. When the UserForm is
showing, click on the new UserForm and look around the VBA window for a
floating toolbox, called, oddly enough, Toolbox. If this is not showing, go
to the View menu and select Toolbox.

In the toolbox, ‘Toolbox’, move and hold your mouse cursor (unclicked) over
each button until the tooltip shows that you have found the ComboBox button.
Click to activate and draw a wide horizontal rectangle on your new UserForm.
Start with approximately the full width of the UserForm and about ½ inch
high. This will create a ComboBox control on the form. A ComboBox control
is a dropdown list that allows you to make selections from the list. Later,
we will ‘populate’ the dropdown list with your Town data.

If it is not already showing, bring up the Properties window (in the VBA
environment, the button for Properties is the small hand holding a sheet of
paper just as in other Microsoft offerings).

The top of the VBA properties window has a ComboBox that enables you to
select what object to show properties for. So far, you have two objects, a
UserForm and a ComboBox on it.

With the properties showing, the name properties of your two objects are
available for you to modify. The name property of the ComboBox appears in the
Appearance category; for the UserForm, it is under the "Misc" category.

You should name both as you will be using these names later. I named my
UserForm “TownSelector†and my ComboBox “TownListâ€. NOTE: it is customary,
and good practice, to use a naming convention, such as frmTownSelector and
cboTownList. If you develop good habits now (unlike me), you will be glad
later as you work on larger projects.

Next. add three CommandButtons to the UserForm, using the same method as for
the CombBox to find and use the CommandButton control in the toolbox,
‘Toolbox’.

Using the Properties window, name the buttons. Mine are “Clear_Formâ€,
“Exit_Buttonâ€, and “PostChoiceâ€. Clearly, no naming conventions were used
here. You might use “btnClearFormâ€, “btnExitâ€, and “btnPostChoiceâ€, or some
such.

Find the Caption property for the buttons and change the “PostChoiceâ€
button’s caption to “Send Choice to Worksheetâ€, the “ClearForm†button’s
caption to “Clear Selectionâ€, and the “Exit_Button†button’s caption to
“Close Selection Boxâ€. Use your button names, of course.

Note that you can also change the color, font and other properties of these
buttons, at least in Excel 2003 and possibly in earlier versions of Excel.
The UserForm can also be made a more eye-pleasing color than gray, if you
wish.

If you have not had to already, you may have to resize the UserForm to
accommodate the next additions. Using the toolbox, ‘Toolbox’, add two
Labels. Resize the two labels to fit the following text: (Label1 is the title
label and should have a large font.)

Contents of the two labels:
Label1: “Selection Boxâ€
Label2:
“Typing in the selection box will bring matching choices, shortening the
path to your selection during dropdown. Use the Clear Selection button to
clear the selection box of the previous selection and enable typing in
letters for a new match.

Send your choice to the worksheet with the send button, you can move this
dialog out of the way and keep trying selections and viewing the result
without closing this selection dialog box. When you have made your final
selection, use the close button to close this form.â€

If you are typing this into the Label, you will find that skipping a line is
not so easy. You can get the linefeed between the subjects in the Label
control by hitting shift and enter keys at the same time. (You might be able
to just paste it in if you want to use what I typed.)

Next we finally get serious. We start to Code. Right click on the
“PostChoice†CommandButton that you placed on you UserForm and select ‘View
Code’ in the dropdown menu. This will take you to a code window with the
cursor in an already prepared but empty Subroutine called “Private Sub
PostChoice_Click()†or rather, “Private Sub [YourButtonName]_Click()†. In
this sub, (above the End Sub line and below the Private Sub line), post the
following code (without the stars).

‘***********************
Dim intNDX As Integer
If intSelected >= 1 Then
intNDX = intSelected + 1
Else
intNDX = 1
End If
With ActiveSheet
rngTarget.Select
rngTarget.Value = aryTowns(intNDX, 1)
Selection.Offset(0, 1).Value = aryTowns(intNDX, 2)
Selection.Offset(0, 2).Value = aryTowns(intNDX, 3)
Selection.Offset(0, 3).Value = aryTowns(intNDX, 4)
Selection.Offset(0, 4).Value = aryTowns(intNDX, 5)
Selection.Offset(0, 5).Value = aryTowns(intNDX, 6)
End With
‘**********************
Note that the intSelected is an integer used to temporarily store the index
number of your selection for later use. Also note the use of “TownListâ€,
which is, if you recall, the name of my ComboBox. You will need to change
this word to whatever you named your ComboBox, leave all else the same.

Note that we are not creating this code in the order that it will be
executed, we have named variables in the previous code that have not yet been
declared. We will take care of all of this as we go.

‘ActiveSheet’ is the worksheet that you will call this form from, your
‘selection and calculation’ worksheet. We are going to declare the Object
variable rngTarget as a Range next and assign it a value later.

The above code that we just pasted into the code window will take the data
belonging to the town that you select from the dropdown list and paste the
data for that town into the five cells to the right of B4, your named range
where you originally put the Town name. As this will replace the current
contents of those cells, which are your VLookup formulas, you might want to
copy these formulas and paste them into a Notepad text file to save them in
case you need them for something else.
I am taking this data from an Array that we will build later, which still
stays in existence, even though I assigned its values to the ComboBox list.
It is easier to extract multi-column data from an Array than a ComboBox, so I
did not close and erase the array after I used it.

Next, go to the top of the code window (just above the button-click
subroutine and outside of and above any subroutine) and paste the following:
(not the stars)

‘***********
Option Base 1
Dim aryTowns() As Variant ’ this is an array
Dim rngTarget As Range
Dim intSelected As Integer
‘***********
Here we have declared our Option Base to be 1, this means that the first
element in arrays will be numbered 1 instead of starting the numbering with
0.
We then declared three variables, an Array, which is an in-memory list or
table of data, an Object variable of the type Range, and an Integer variable
to store the list position index number of your selected town.

We can assign an actual worksheet range to an object data type, that range
can consist of a single cell or a multiple cell selection, and thereby expose
the properties of this Range to our program.

The properties that we are interested in are this single cell range’s
location on the page, to guide us as to where you want to put the other data,
and the cell’s contents or value, which we wish to set from your selection.

At the top of the code window, above the Option and Dim declarations you
just pasted, are two ComboBoxes (dropdown lists). Since you just pasted the
declarations, the left ComboBox should have the word “(General)†in it and
the right one should have “(Declarations)â€.

Dropdown the left ComboBox at the top and select “UserFormâ€; then drop down
the right ComboBox and select “initializeâ€. You should get a new subroutine,
“Private Sub UserForm_Initialize()â€. This subroutine does not have to use the
UserForm’s name because it is private to this form and not visible outside of
this form. What will happen is that whatever code is in this subroutine will
run when, and each time, the form is first called up or initialized.

In this empty subroutine, between the “Private Sub UserForm_Initialize()â€
line and the “End Sub†line, paste this code: …(Not the stars)

‘**********
Set rngTarget = Range("DataTarget")
aryTowns = Range("Pop_Data2")
TownList.List = aryTowns
‘**************

Remember that I named my ‘B4’ cell (where the Town name goes) on the
selection/calculation page to “DataTargetâ€. This code above sets the range
object variable ‘rngTarget’ to that named range. (Object variables have to be
‘Set’, to objects compatible to their type, not just assigned with a plain
old equal sign.)
This code also assigns the values in the named Range that contains my data
table to the Array variable ‘aryTowns’. I named this table ‘Pop_Data2’ in my
sheet; use your table’s range name to replace ‘Pop_Data2’ here.
Immediately after the code line assigning the table data in the named range
to the Array, the next line assigns that same Array to the ‘List’ property of
the ComboBox which is named ‘TownList’. Replace the word ‘TownList’ with
whatever you named your ComboBox.

Note that this will be the first code in the form to run, but since it is
event-triggered, it does not matter if it is not the first or top subroutine
in the code window, so you can leave it wherever it has landed.

Next, go back to the left side ComboBox at the top of the code window, and
pull down and select the name of your clear-form button. Select ‘Click’ in
the right hand ComboBox. You should get something like this:

Private Sub Clear_Form_Click()

End Sub

Inside of this, paste the following code:

‘********
TownList.ListIndex = 2
TownList.Text = ""
TownList.SetFocus
‘***********
That is two double-quotation marks, right next to each other, no space.

When done, it should look something like this:

Private Sub Clear_Form_Click()
TownList.ListIndex = 2
TownList.Text = ""
TownList.SetFocus
End Sub

Substitute the name of your ComboBox here. This just routine clears the
ComboBox of previous selections so that you can more easily type into it;
typing a few letters of the town name speeds up the selection process greatly.

Next, go back to the built-in ComboBox at the top left of the code window
and select the ‘Exit’ button. (Mine is named ‘Exit_Button’, yours may be
named just ‘Exit’ or ‘btnExit’. You should get something like:

Private Sub Exit_Button_Click()

End Sub

This takes the single line below, substitute your UserForm name here.
********
Unload TownSelector
********
To give you:

Private Sub Exit_Button_Click()
Unload TownSelector
End Sub

This code just closes the UserForm.

NEW MATERIAL:
You need one more routine. On testing, the form worked one evening and
failed to perform the next afternoon. The index property (where it stores the
line number of the item that you select) was being saved the first time I
tested it, so I could use that number to retrieve the same line in the data
list kept in the Array.

This changed and now only the item selected is being saved in the ComboBox
properties; so in (and only in) cases where there are repeats of town names
(For instance, there are several Aberdeen’s in different states), and you
select a town that is not the first one in the list, then the index is re-set
to the first instance of the duplicated name (just like those stinkin’
VLookups). So you need to add a piece of code to capture this line index
number at selection time.

First you need to go back to the top of the code window where those two
built-in ComboBoxes are and, in the right hand box, select the name of your
ComboBox. Then you need to go to the dropdown on the right and select
‘Change’ from the list of events.

You should get something that looks like this, only with your ComboBox name
in it:

Private Sub TownList_Change()

End Sub

Inside of this, place your version of the following single line of code:

***********
intSelected = TownList.ListIndex
*************

It would look something like this:

Private Sub TownList_Change()
Debug.Print "ListChange Index: "; TownList.ListIndex
intSelected = TownList.ListIndex
End Sub

END OF COMPLETELY NEW MATERIAL, Some of the following may have been edited
from the original.

You should now have one set of declarations and five subroutines, at least
one subroutine for each control (One each for the buttons and two for the
dropdown box) on the UserForm. There is no error trapping code, it really is
not necessary for a simple task like this, unless you are going to distribute
this as an application. Leave that for the future, this is tested code,
except for using your object and range names in the correct places.
You are done coding the UserForm.

Next, you need to go back to the Object View and look at the UserForm again.
In the top of the VBA window which your code window is hosted by, Click
‘View|Object’ to bring the UserForm back into design mode. Your properties
box should still be showing, if not, call it back up. Now, click on the
ComboBox.

In the ComboBox properties, AutoWordSelect should be True, MatchEntry should
be 1 – fmMatchEntryComplete, ColumnCount should be 6, and ColumHeads true.
You may have to set ColumWidths to match your data, experimentation will
tell you how much. My columns are 300 pt; 150 pt; 100 pt; 90 pt; 80 pt; 100
pt. That may be wider than you need. Just type in the numbers that you want,
with comas, VBA will supply the ‘pt’ and the semi-colons. Experiment later
when it is all running to see if you need to adjust the column widths.

My ComboBox’s ControlSource property is ‘DataTarget’ (no quote marks), which
is the name of the cell B4 on the selection/lookup/calculation page. The
range name given to that cell can be just typed into this property in the
property box.

I set my ListRows property to 20, I do not like having just the default of 8
rows showing when I drop down the ComboBox.
As for the rest, you can adjust the font here; also note that you may need
to adjust the size and positioning of the ComboBox to accommodate the six
columns of data you need to show.

Other than messing around with control sizing and positioning, and form and
control formatting, you should be pretty much finished with the UserForm.

Leave the VBA environment open for now, you may have to go back to it to
adjust the ComboBox later. For now, switch windows to the Excel workbook,
specifically to the Selection/Lookup/Calculation worksheet.

You need to create a CommandButton on this worksheet to use to call up the
UserForm. With the lookup/calculation worksheet showing, Click
‘View|ToolBars’ and see that ‘Control Toolbox’ is selected. Once that toolbar
is showing, on the Control Toolbox, click the ‘Design Mode’ button (it has a
triangle, a ruler, and a pencil on it).

Once the worksheet is in Design Mode, go back to the same toolbar and click
the CommandButton control to add a CommandButton to your worksheet. Select a
convenient area that is visible and draw the button there (mine is placed
right under B4, the cell that is named DataTarget).

Go back to the same toolbar and select Properties, again this is the button
with a hand holding a sheet of paper. In the properties window, with the
worksheet still in design mode, select the CommandButton.

In the new CommandButton’s properties, set the caption to “Update Town Dataâ€
or something you will recognize. You might prefer something like “Change
Townâ€. In the ‘(Name)’ property, name the CommandButton; mine is named
‘UpdateData’ yours might be ‘btnUpdateData’. Adjust other properties to taste.

Now, right-click on the CommandButton. Select ‘View Code’. This should take
you to a new code window (this code window belongs to, and is private to,
this worksheet.)

Only one Subroutine should be showing:

Private Sub UpdateData_Click()

End Sub

Yours should see the name you gave your button instead of “UpdateDataâ€.
Inside this empty subroutine, paste:

‘***********
TownSelector.Show
‘**********

The only Subroutine showing in this code window should now be this:

Private Sub UpdateData_Click()
TownSelector.Show
End Sub

You are finished here and you are back in the VBA environment, so switch
back to the Worksheet window and to the lookup/calculation worksheet where
the new CommandButton is.
Take the worksheet out of Design Mode. Hold your breath, cross your eyes and
fingers, stand on your head and hop on one foot all at the same time, just
for luck, then click the button. When the smoke clears and you get your new
computer installed, let me know what happened.

Really, it should work, barring missing a place or so where you need to
change an object name or a range name in the code. Let me know if you need
help troubleshooting it.

Let us know if this helps or if you need further help or clarification. I
do not really believe that we will get this in one try; too many steps, but
keep updating me of where you are in the process and I should be able to
further explain where this is deficient.
SongBear
 
B

Bruge

Hi SongBear

Thanks a million - that works well - so much so that with careful tweaking
of the position of the Calculation boxes etc I can use the main "TownList"
Combo box as a search tool without entering data into B4 (A4 on my worksheet)
and after selecting the respective Town etc, close the UserForm and bingo my
Gravity Compensation is calculated.
--
Bruge


SongBear said:
CORRECTED COPY:

Bruge,
You will want to replace VLookup with something that will allow you to
Correctly access a table with duplicates in the first column. VLookup will
only find the first instance of duplicates even if you select the second of
the same names to put into the Town box. And you are typing-in and not
selecting from a list, anyway. Below is the complete solution to this problem,

To do this you would be well served by a UserForm which we will create now.

Open the workbook with the list and the selection/calculation worksheets
and, if you have not already, on the worksheet where you are typing in the
town name and using the VLookup formulas, select the single cell B4 (or the
cell where you put the town name; the data cells containing your VLookup
formulas should be just to the right, if I understand you correctly), and
create a range name for that cell, I called B4 on my lookup/calculate
worksheet: “DataTargetâ€. You will see why it is important to have this cell
named later.

On the data sheet, select the headers of the table of town names and info
and shift/control/down to select the table, then give the entire table a
range name; I called mine ‘Pop_Data2’ because I downloaded a list of towns
and populations from the US census web cite to use to build and test this.

Open the VBA Editor (Tools|Macro|Visual Basic Editor). In the VBA
programming environment, click Insert|UserForm. When the UserForm is
showing, click on the new UserForm and look around the VBA window for a
floating toolbox, called, oddly enough, Toolbox. If this is not showing, go
to the View menu and select Toolbox.

In the toolbox, ‘Toolbox’, move and hold your mouse cursor (unclicked) over
each button until the tooltip shows that you have found the ComboBox button.
Click to activate and draw a wide horizontal rectangle on your new UserForm.
Start with approximately the full width of the UserForm and about ½ inch
high. This will create a ComboBox control on the form. A ComboBox control
is a dropdown list that allows you to make selections from the list. Later,
we will ‘populate’ the dropdown list with your Town data.

If it is not already showing, bring up the Properties window (in the VBA
environment, the button for Properties is the small hand holding a sheet of
paper just as in other Microsoft offerings).

The top of the VBA properties window has a ComboBox that enables you to
select what object to show properties for. So far, you have two objects, a
UserForm and a ComboBox on it.

With the properties showing, the name properties of your two objects are
available for you to modify. The name property of the ComboBox appears in the
Appearance category; for the UserForm, it is under the "Misc" category.

You should name both as you will be using these names later. I named my
UserForm “TownSelector†and my ComboBox “TownListâ€. NOTE: it is customary,
and good practice, to use a naming convention, such as frmTownSelector and
cboTownList. If you develop good habits now (unlike me), you will be glad
later as you work on larger projects.

Next. add three CommandButtons to the UserForm, using the same method as for
the CombBox to find and use the CommandButton control in the toolbox,
‘Toolbox’.

Using the Properties window, name the buttons. Mine are “Clear_Formâ€,
“Exit_Buttonâ€, and “PostChoiceâ€. Clearly, no naming conventions were used
here. You might use “btnClearFormâ€, “btnExitâ€, and “btnPostChoiceâ€, or some
such.

Find the Caption property for the buttons and change the “PostChoiceâ€
button’s caption to “Send Choice to Worksheetâ€, the “ClearForm†button’s
caption to “Clear Selectionâ€, and the “Exit_Button†button’s caption to
“Close Selection Boxâ€. Use your button names, of course.

Note that you can also change the color, font and other properties of these
buttons, at least in Excel 2003 and possibly in earlier versions of Excel.
The UserForm can also be made a more eye-pleasing color than gray, if you
wish.

If you have not had to already, you may have to resize the UserForm to
accommodate the next additions. Using the toolbox, ‘Toolbox’, add two
Labels. Resize the two labels to fit the following text: (Label1 is the title
label and should have a large font.)

Contents of the two labels:
Label1: “Selection Boxâ€
Label2:
“Typing in the selection box will bring matching choices, shortening the
path to your selection during dropdown. Use the Clear Selection button to
clear the selection box of the previous selection and enable typing in
letters for a new match.

Send your choice to the worksheet with the send button, you can move this
dialog out of the way and keep trying selections and viewing the result
without closing this selection dialog box. When you have made your final
selection, use the close button to close this form.â€

If you are typing this into the Label, you will find that skipping a line is
not so easy. You can get the linefeed between the subjects in the Label
control by hitting shift and enter keys at the same time. (You might be able
to just paste it in if you want to use what I typed.)

Next we finally get serious. We start to Code. Right click on the
“PostChoice†CommandButton that you placed on you UserForm and select ‘View
Code’ in the dropdown menu. This will take you to a code window with the
cursor in an already prepared but empty Subroutine called “Private Sub
PostChoice_Click()†or rather, “Private Sub [YourButtonName]_Click()†. In
this sub, (above the End Sub line and below the Private Sub line), post the
following code (without the stars).

‘***********************
Dim intNDX As Integer
If intSelected >= 1 Then
intNDX = intSelected + 1
Else
intNDX = 1
End If
With ActiveSheet
rngTarget.Select
rngTarget.Value = aryTowns(intNDX, 1)
Selection.Offset(0, 1).Value = aryTowns(intNDX, 2)
Selection.Offset(0, 2).Value = aryTowns(intNDX, 3)
Selection.Offset(0, 3).Value = aryTowns(intNDX, 4)
Selection.Offset(0, 4).Value = aryTowns(intNDX, 5)
Selection.Offset(0, 5).Value = aryTowns(intNDX, 6)
End With
‘**********************
Note that the intSelected is an integer used to temporarily store the index
number of your selection for later use. Also note the use of “TownListâ€,
which is, if you recall, the name of my ComboBox. You will need to change
this word to whatever you named your ComboBox, leave all else the same.

Note that we are not creating this code in the order that it will be
executed, we have named variables in the previous code that have not yet been
declared. We will take care of all of this as we go.

‘ActiveSheet’ is the worksheet that you will call this form from, your
‘selection and calculation’ worksheet. We are going to declare the Object
variable rngTarget as a Range next and assign it a value later.

The above code that we just pasted into the code window will take the data
belonging to the town that you select from the dropdown list and paste the
data for that town into the five cells to the right of B4, your named range
where you originally put the Town name. As this will replace the current
contents of those cells, which are your VLookup formulas, you might want to
copy these formulas and paste them into a Notepad text file to save them in
case you need them for something else.
I am taking this data from an Array that we will build later, which still
stays in existence, even though I assigned its values to the ComboBox list.
It is easier to extract multi-column data from an Array than a ComboBox, so I
did not close and erase the array after I used it.

Next, go to the top of the code window (just above the button-click
subroutine and outside of and above any subroutine) and paste the following:
(not the stars)

‘***********
Option Base 1
Dim aryTowns() As Variant ’ this is an array
Dim rngTarget As Range
Dim intSelected As Integer
‘***********
Here we have declared our Option Base to be 1, this means that the first
element in arrays will be numbered 1 instead of starting the numbering with
0.
We then declared three variables, an Array, which is an in-memory list or
table of data, an Object variable of the type Range, and an Integer variable
to store the list position index number of your selected town.

We can assign an actual worksheet range to an object data type, that range
can consist of a single cell or a multiple cell selection, and thereby expose
the properties of this Range to our program.

The properties that we are interested in are this single cell range’s
location on the page, to guide us as to where you want to put the other data,
and the cell’s contents or value, which we wish to set from your selection.

At the top of the code window, above the Option and Dim declarations you
just pasted, are two ComboBoxes (dropdown lists). Since you just pasted the
declarations, the left ComboBox should have the word “(General)†in it and
the right one should have “(Declarations)â€.

Dropdown the left ComboBox at the top and select “UserFormâ€; then drop down
the right ComboBox and select “initializeâ€. You should get a new subroutine,
“Private Sub UserForm_Initialize()â€. This subroutine does not have to use the
UserForm’s name because it is private to this form and not visible outside of
this form. What will happen is that whatever code is in this subroutine will
run when, and each time, the form is first called up or initialized.

In this empty subroutine, between the “Private Sub UserForm_Initialize()â€
line and the “End Sub†line, paste this code: …(Not the stars)

‘**********
Set rngTarget = Range("DataTarget")
aryTowns = Range("Pop_Data2")
TownList.List = aryTowns
‘**************

Remember that I named my ‘B4’ cell (where the Town name goes) on the
selection/calculation page to “DataTargetâ€. This code above sets the range
object variable ‘rngTarget’ to that named range. (Object variables have to be
‘Set’, to objects compatible to their type, not just assigned with a plain
old equal sign.)
This code also assigns the values in the named Range that contains my data
table to the Array variable ‘aryTowns’. I named this table ‘Pop_Data2’ in my
sheet; use your table’s range name to replace ‘Pop_Data2’ here.
Immediately after the code line assigning the table data in the named range
to the Array, the next line assigns that same Array to the ‘List’ property of
the ComboBox which is named ‘TownList’. Replace the word ‘TownList’ with
whatever you named your ComboBox.

Note that this will be the first code in the form to run, but since it is
event-triggered, it does not matter if it is not the first or top subroutine
in the code window, so you can leave it wherever it has landed.

Next, go back to the left side ComboBox at the top of the code window, and
pull down and select the name of your clear-form button. Select ‘Click’ in
the right hand ComboBox. You should get something like this:

Private Sub Clear_Form_Click()

End Sub

Inside of this, paste the following code:

‘********
TownList.ListIndex = 2
TownList.Text = ""
TownList.SetFocus
‘***********
That is two double-quotation marks, right next to each other, no space.

When done, it should look something like this:

Private Sub Clear_Form_Click()
TownList.ListIndex = 2
TownList.Text = ""
TownList.SetFocus
End Sub

Substitute the name of your ComboBox here. This just routine clears the
ComboBox of previous selections so that you can more easily type into it;
typing a few letters of the town name speeds up the selection process greatly.

Next, go back to the built-in ComboBox at the top left of the code window
and select the ‘Exit’ button. (Mine is named ‘Exit_Button’, yours may be
named just ‘Exit’ or ‘btnExit’. You should get something like:

Private Sub Exit_Button_Click()

End Sub

This takes the single line below, substitute your UserForm name here.
********
Unload TownSelector
********
To give you:

Private Sub Exit_Button_Click()
Unload TownSelector
End Sub

This code just closes the UserForm.

NEW MATERIAL:
You need one more routine. On testing, the form worked one evening and
failed to perform the next afternoon. The index property (where it stores the
line number of the item that you select) was being saved the first time I
tested it, so I could use that number to retrieve the same line in the data
list kept in the Array.

This changed and now only the item selected is being saved in the ComboBox
properties; so in (and only in) cases where there are repeats of town names
(For instance, there are several Aberdeen’s in different states), and you
select a town that is not the first one in the list, then the index is re-set
to the first instance of the duplicated name (just like those stinkin’
VLookups). So you need to add a piece of code to capture this line index
number at selection time.

First you need to go back to the top of the code window where those two
built-in ComboBoxes are and, in the right hand box, select the name of your
ComboBox. Then you need to go to the dropdown on the right and select
‘Change’ from the list of events.

You should get something that looks like this, only with your ComboBox name
in it:

Private Sub TownList_Change()

End Sub

Inside of this, place your version of the following single line of code:

***********
intSelected = TownList.ListIndex
*************

It would look something like this:

Private Sub TownList_Change()
Debug.Print "ListChange Index: "; TownList.ListIndex
intSelected = TownList.ListIndex
End Sub
Click to expand...
 
Top