Replies

Perhaps you have non-numeric values in between the numbers? The formula I posted DOES sum the last 10 cells starting from the last non-blank cell upwards.

You are correct. The mistake that I’ve made was to insert a new row after I assigned the formula. That made A1 go to B1, and so it summed an empty cell.

For column A, you can use the following formula:

=SUM(OFFSET(A$1,MATCH(9.99999999999999E+307,A:A)-10,0,10,1))

For column F, change the references from A to F.
If you need to sum the last 15 cells instead of the last 10, change both occurrences of 10 to 15.

Note: 9.99999999999999E+307 is the largest number you can enter in a cell.

Great. Thanks. I found that with this formula, it actually sums the last 9 numbers rather than 10, so I changed the ’10’ to ’11’.

I have attached an example of what I currently have. The problem I have is that when I click on the N/A checkbox I get a message that I setup in the textbox event. How can I fix this so it will work properly?

You could use the Exit event to validate the contents. If they don’t match, pop up a message and set the Cancel argument to True.

Can you give me an example?

In the second example I should have had

Dim wsh As Worksheet

instead of

Dim wsh As Range

That did it. Thanks.

Thanks Hans. I now get a “Run-time error ’13’: Type mismatch” at this line:

With frmProperties

Do you know why?

Any idea why I get a “Run-time error ‘1004’: Application-defined or Object-defined error” in this line of the module:

Sub ShowDialog()

With frmProperties ‘ <<< Error shows up here
.cmbType.RowSource = ""
.lstName.RowSource = ""
.lstFinish.RowSource = ""
.lstProperties_BOL.RowSource = ""
.lstProperties_EOL.RowSource = ""
.lstName.MultiSelect = fmMultiSelectMulti
.lstFinish.MultiSelect = fmMultiSelectMulti
.lstProperties_BOL.MultiSelect = fmMultiSelectExtended
.lstProperties_EOL.MultiSelect = fmMultiSelectExtended
End With

' Assign material names to ComboBox
GetMaterialNames
' Show the UserForm
frmProperties.Show
End Sub

When I use the following code in the userform module?

Private Sub UserForm_Initialize()

' Worksheets("Properties Database").Activate
' Set rngAllMaterialTypes = ActiveWorkbook. _
Worksheets("Properties Database"). _
Range("A5", Range("A65536").End(xlUp)) ' <<<< WORKS when removing the two comment signs

Set rngAllMaterialTypes = ActiveWorkbook. _
Worksheets("Properties Database"). _
Range("A5", Range("A65536").End(xlUp)) ' <<<< DOESN'T WORK

One last question. As you can see from the attached figure, I get these dotted lines that disrupt the view in the listbox when I scroll from left to right, and back again. Any idea how I can avoid this from happening?

If you set the ColumnWidths property of the list boxto a large enough number, the list box will display a horizontal scroll bar.
It’s not easy, however, to make the ColumnWidths just large enough to fit the text, since you probably use a proportional font.

Thanks Hans. Can you control the appearance of a horizontal scroll bar such that if the text fits within the current width of listbox then a scroll bar won’t show up, and if the full length of the text that appears within the listbox is beyond the width of the listbox then a horizontal scroll bar would appear? If so, how?

Round is a function, so you must assign its return value to a variable:

Dim dblVal As Double
If IsNumeric(strABOL) Then dblVal = Round(strABOL, 2)

(Please note that it is IsNumeric, not IsNumberic)

Great. Thanks Hans.

You’ve gone overboard with the Ranges. Change

Set rngMaterialName = Range(Range(Range(“A5”), Range(“A65536”).End(xlUp)))

to

Set rngMaterialName = Range(Range(“A5”), Range(“A65536”).End(xlUp))

and in lsType_Change, you’re missing an End If between Next rng and Next rngName (the indentation of the code is not correct there)

Thanks again Hans, great help!

For a multi-select list box you shouldn’t use the Click event but the Change event.
You can’t just keep on adding items to lsFinish each time the user clicks an item. You’ll have to clear lsFinish and determine which items should be added.

I now get an error when I try to run the userform: “Run-time error ‘1004’: Method ‘Range’ of object ‘_Global’ failed”

Any idea why?

You can add the items to a Collection object first, with an error handler to suppress the error that occurs when you try to add the same value twice. Then add the members of the Collection to the list box. See Fill a ListBox with unique values from a worksheet using VBA in Microsoft Excel for a code example.

One more question. I have setup the userform as shown in the attached figure. For each selection of Material, I get a list of material types. But when I click on this list box (lsType) nothing shows up on the material finish listbox (lsFinish). Any ideas why?

Clear the list box.
Loop through the filled cells in column A.
Each time the value equals “Aluminum”, use AddItem to add the value from column B to the list box.

I got it, thanks Hans. How would I remove duplicates from the listbox?

Yes, that would make it easier – I did the same before importing the table into Access (I also removed the blank rows)

I still not too clear on how to make this in Excel. Let me simplify it further: If I have the following table in Excel in the range from A5 to D9:
Aluminum Al 6061 Anodized 0.50
Aluminum Al 6061 Anodized 1200S 0.49
Aluminum Al 7075-T6 Clear Anodized 0.81
Stainless Steel / CRES Stainless Steel Polished 0.42
Stainless Steel / CRES Stainless Steel Sandblasted 0.58

What would be the VBA code if I was looking for the list of material types for, let’s say, Aluminum?