Need a book? Engineering books recommendations...

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

RE: Great ASCE7 spreadsheet

[Subject Prev][Subject Next][Thread Prev][Thread Next]

Curious as to why people use the combobox from the control toolbox, rather than use the simpler data validation list. I only ever use ComboBox’s when programming and using forms in VBA, or creating forms in Access. It is far easier to use data validation in the worksheet and it copies, and doesn’t add too much to the filesize, nor slow the worksheet down, which floating objects tend to do. Data validation drop down lists also do not mess up the printout, the list button only appears when you select the cell. (Cells can be merged to make wider). Simply create a worksheet to contain lists, name the whole range of each list, then use the range name, when setting data validation. Data validation also permits adding error messages, prompts to the worksheet cell. All fast and easy.


What I have noticed however is when command buttons from the control toolbox are placed on the worksheet, then there is incompatibility between Excel 97, XP and 2003, even though they are all supposed to be using the same file format. That incompatibility results in Excel not recognizing the “control”, it won’t allow deletion of the control because it doesn’t recognize its presence, but then won’t allow adding a new “control” because there is some part of it that it does recognize.


Usually end up having to rebuild the whole workbook, copying/moving worksheets into a new workbook, whilst only copying cells from the worksheet containing the troublesome control. Having done that a few times, I avoid using controls on the worksheet, or otherwise maintain two versions of the workbook: one without the controls and one without.



Conrad Harrison

B.Tech (mfg & mech), MIIE, gradTIEAust



South Australia

From: Mark Johnson [mailto:markajohn(--nospam--at)]
Sent: Wednesday, 5 December 2007 02:09
To: Glen Pappas
Cc: seaint(--nospam--at)
Subject: Re: Great ASCE7 spreadsheet


Glen and List,

Thanks for your permission to forward this to the SEAINT List.  It should be helpful for anyone who wants to use this tool.  I had exactly the same difficulty with the combo boxes as you did when I copied the spread sheets.  I'm still hoping someone will crack it for us.

Mark Johnson

----- Original Message ----
From: Glen Pappas <gpappas(--nospam--at)>
To: markajohn(--nospam--at)
Sent: Tuesday, December 4, 2007 7:04:07 AM
Subject: RE: Great ASCE7 spreadsheet

Hello, Mark.  I saw your e-m & it rang a bell, so I did some digging & found e-m below. 

Although we're obviously not talking about the exact same software (i.e., for one thing, what you're looking at is IBC '06, whereas I was looking at '03) hopefully the e-m will be of some use to you anyway?

Feel free to share w/rest of SEAINT List.


Date: Mon, 13 Feb 2006 08:05:26 -0800
From: "Yo Ratanapeanchai" <ratany(--nospam--at)>
To: gpappas(--nospam--at)
Subject: Re: IBC 2003 Spreadsheet

Hi Glen:

I am must be drank when I wrote this Snow Load spreadsheet. I was using ASCE7-1998 edition instead of 2002 edition. The Equation 7-3 in 1998, the value of beta base on L and W dimension and completely difference from 2002 edition. However the L,Lr,S sheet is not a password protected, so you can unprotect and modify to correct vision of the code. I will revise the spreadsheet as soon as possible. thanks for catching an error.
>>> "Glen Pappas" <gpappas(--nospam--at)> 2/10/06 1:35:21 PM >>>
Yo, I've learned that creating Combo Box isn't trivial!  I've put that on
hold for a while, & tried using your spreadsheet as is.

On the L, Lr, S  sheet, why doesn't the value of beta change as the ground
snow load (pg) changes?  You correctly indicate that beta is determined
based on Eq. 7-3, but regardless of the the value of pg that I specify,
beta remains 0.89.  Beta should be 0.89 only for pg = 24.4 psf.


>>Date: Tue, 07 Feb 2006 14:09:55 -0800
>>From: "Yo Ratanapeanchai" <ratany(--nospam--at)>
>>To: gpappas(--nospam--at)
>>Subject: Re: IBC 2003 Spreadsheet

>>The only thing that copy and paste command will not copy is the drop down
>>menu (Combo box from Control tool box). But you can create the Combo Box
>>after you copy the rest of spreadsheet. The password only protects the
>>user from modifying the original spreadsheet. If you know how the Combo
>>box works then it should be very easy to modify the rest. I'd rather
>>not  discuss how Excel works. Thanks.
>>Yo Ratanapeanchai
>> >>> "Glen Pappas" <gpappas(--nospam--at)> 2/7/06 12:25 PM >>>
>>Hello, Yo.  You've done a great job getting the IBC into a spreadsheet!
>>I'm having trouble trying to copy your sheet so that I can change some
>>values (e.g., the "0.00256" value for qz).  I believe I followed the
>>instructions in your Read Me file but, when I pasted, I lost the pull-down
>>menus & the values you have in blue.  I had macros enabled w/your sheet
>>before I copied.
>>Could the problem be that the macros are password-protected?  If so, can
>>you send them to me so that I can insert them in the appropriate places in
>>my copy of your spreadsheet?


Glen Pappas, Ph.D., PE, SECB, BSCP
Los Alamos National Laboratory
Technical Staff Member
Phone: 505-665-1221
Fax: 505-665-4728
MS M791