Need a book? Engineering books recommendations...

Return to index: [Subject] [Thread] [Date] [Author]

RE: Great ASCE7 spreadsheet / ComboBoxes

[Subject Prev][Subject Next][Thread Prev][Thread Next]
For those having problems with comboboxes, I did some experimenting to check
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

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.

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. 


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.

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

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.

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

>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"
    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.

Conrad Harrison
B.Tech (mfg & mech), MIIE, gradTIEAust
South Australia 

******* ****** ******* ******** ******* ******* ******* ***
*   Read list FAQ at:
*   This email was sent to you via Structural Engineers 
*   Association of Southern California (SEAOSC) server. To 
*   subscribe (no fee) or UnSubscribe, please go to:
*   Questions to seaint-ad(--nospam--at) 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: 
******* ****** ****** ****** ******* ****** ****** ********