the differences between Data Validation and ComboBoxes. Thankyou to Bill
Allen and Dimtri Wright for their earlier responses.
As an example of use: downunder we have a simplified wind code for
residential AS4055. It consists of wind classes and design wind speeds as
follows:
WindClassList WindSpeedList
N1 34
N2 40
N3 50
N4 61
N5 74
N6 86
C1 50
C2 61
C3 74
C4 86
I use the column titles are as Range names for each column.
Range Name 'TableWindClass' given to both columns of data excluding headers.
DATA VALIDATION
-------------------------------
My usual approach would be to set data validation in a cell.
Menu: /Data/Validation.
Allow: List
Source: =WindClassList (For short lists it is possible to simply type a
comma delimited list into the source editbox.)
This cell given rangeName WindClass. Then use the worksheet function vlookup
to get the value of the windspeed, 2nd column of the table.
=VLOOKUP(WindClass,TableWindClass,2,0)
More complicated tests can be achieved using combination of lookup, hlookup,
vlookup, index, match, address, indirect and if functions. Most of time
using the reverse process: calculate wind speed to detailed wind code
(AS1170.2), then assign wind class to AS4055, for those who need the wind
class to select standard products like windows. To do this use 4 column
table, use vlookup on inverse of wind speed, and an 'if' condition to change
columns which retrieve data from depending on whether cyclonic or
non-cyclonic wind class.
I generally place my lookup tables on separate worksheets, and in separate
workbooks, thus creating a centralised reference source. Though Excel
doesn't allow creation of such. Get around this by creating in one workbook
and then moving worksheet to central data source workbook.
Also I'm not sure, but I think Bill Allen was suggesting that can use
worksheet functions when identifying the source for the data list, I've
never tried this, and couldn't think of an example. Alternatively may be he
was suggesting using "indirect" function within the worksheet where the list
and lookup table is defined. Basically it is possible to use a series of
worksheet functions, to retrieve data from complicated tables. However, for
ease of use, and readability, I prefer swapping to VBA functions, when
setting up the worksheet gets too convoluted. But choice depends on how
quick I want the answer and how often.
WORKSHEET COMBOBOX
------------------------------------------
Using a worksheet combobox however can skip the use of vlookup, and have a
two column combobox and return windspeed directly. If don't mind the
combobox printing. (Or set PrintObject=false, if don't want to print value
selected.)
For the properties set:
LinkCell: Vu 'result field
ListFillRange: TableWindClass
BoundColumn: 2 'this displays column 2 in result field (windspeed=Vu)
ColumnCount: 2
TextColumn: 1 'this displays column 1 in the Combobox (WindClass)
if TextColumn=0 then returns list row index, rather than value in the list.
Also note the value displayed by the ComboBox is not a value in a worksheet
cell, it cannot be used in worksheet formula, only the value returned in
LinkCell can be used in formula.
I'm not sure what index number Dimtri Wright was referring to, but his
reference to IF-THEN suggests access from within VBA code rather than
something that can be accessed directly from within a worksheet. In VBA can
access ListIndex and/or Value of the combobox object.
VBA FORMS
---------------------
Alternatively if using VBA and forms:
Insert new form, add the ComboBox and set properties:
ControlSource: Vu
RowSource: TableWindClass
BoundColumn: 2 'this displays column 2 in result field (windspeed=Vu)
ColumnCount: 2
can leave
TextColumn: -1 'this displays first column with display width greater than
zero
From within VBA can also address properties/methods: .List, .ListCount,
.ListIndex. This allows using the ARRAY function to generate the list and
assign it directly to: object.list. Which is generally how I've always
initialised VBA forms.
Adding VBA behind the ComboBoxes
--------------------------------------------------------
The only problem with the two ComboBox object/methods is changing the cell
value for Vu updates the control. The controls will return first value in
list.(eg. WindClass N3 has same speed as WindClass C1) That is if type 50
into cell for Vu, the control will update to display N3. But not a major
problem, could define different lists, for cyclonic and non-cyclonic, and
have VBA modify the control based on the selection. Something like:
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
ComboBox2.ListFillRange = "TableCyclonicSpeeds"
Else
ComboBox2.ListFillRange = "TableNonCyclonicSpeeds"
End If
End Sub
'with more meaningful names for the controls of course.
On the other hand most of the time I use the detailed wind loading code
AS1170.2, and even if using AS4055 first have to determine what the wind
class is before can go selecting it. So I use a different set of tables. It
was just something simple to illustrate ComboBoxes, and there have been
several threads on complexity of ASCE7 and requests for simplified wind
loading for smaller structures. So here is an example of simple code AS4055.
Though it does require the need to consider Region, Terrain, Topography, and
shielding: it is all done descriptively and is compatible with AS1170.2.
Importance is locked for residential (normal) buildings. Though much simpler
if can just get wind class, from a map or from local council. Normally use
windclass as described above if designing something like a standard carport
for windclass N2 or similar, in which case it becomes a matter of assessing
the site and seeing if standard structure suitable. Maybe worth taking a
look at AS4055 for those looking for a simpler code to ASCE7, and derive
similar from ASCE7.
Does not explain however why many people use ComboBoxes with no VBA code,
when data validation is easier to use, more stable and more portable.
Workbooks with floating objects demand a lot of RAM, and may also require
changing the COM automation references (in the VBA editor), reducing the
portability of the workbook between different machines and/or different
versions/releases of Excel.
In any case provides a rough guide to the various ways of providing a drop
down selection list.
Regards
Conrad Harrison
B.Tech (mfg & mech), MIIE, gradTIEAust
mailto:sch.tectonic@bigpond.com
Adelaide
South Australia
******* ****** ******* ******** ******* ******* ******* ***
* Read list FAQ at: http://www.seaint.org/list_FAQ.asp
*
* This email was sent to you via Structural Engineers
* Association of Southern California (SEAOSC) server. To
* subscribe (no fee) or UnSubscribe, please go to:
*
*
http://www.seaint.org/sealist1.asp
*
* Questions to seaint-ad@seaint.org. Remember, any email you
* send to the list is public domain and may be re-posted
* without your permission. Make sure you visit our web
* site at: http://www.seaint.org
******* ****** ****** ****** ******* ****** ****** ********