PDA

View Full Version : VBA Run-Time Error only on program close



JoshuaPeters
10-04-2011, 10:10 AM
I am attempting to make a program that can calculate or check beam sizes for different loads. Part of this includes a Combo box with a number of beam sizes that can be chosen and tested to see if they work. The Combo box seems to work when the program is open, but when I close Excel I get an error box with the error:
Runtime error 1004: method 'Worksheets' of object'_Global failed

on the debugger it picks out the line with the arrow beside it below


Private Sub BeamComboBox_Change()
'Fills in the geometric section properties or clears and highlights the cells for program optimization

Dim i As Integer

i = Worksheets("Input").Range("BeamSelection") <--- This line is highlighted

Application.ScreenUpdating = False

'Clear geometric properties
If i = 1 Then
CalculateBeam.Value = True
With Worksheets("Input").Range("L23:L36")
.Clear
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = 0
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
End With
Worksheets("Input").Range("J19:O37").NumberFormat = ";;;"

'input geometric properties of chosen beam
Else
ChooseBeam.Value = True
With Worksheets("Input").Range("L23:L36")
.Clear
.Font.Bold = True
.HorizontalAlignment = xlCenter
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
End With
'
'The following uses the Excel lookup function to import the section geometry from the database
With Sheets("Input")
.Range("d") = WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 4, False)
.Range("w") = WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 7, False)
.Range("b") = WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 5, False)
.Range("t") = WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 6, False)
.Range("Ast") = WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 9, False)
.Range("Ix") = (WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 10, False)) * 10 ^ 6
.Range("Sx") = (WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 11, False)) * 10 ^ 3
.Range("Zx") = (WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 12, False)) * 10 ^ 3
.Range("Iy") = (WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 13, False)) * 10 ^ 6
.Range("J") = (WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 17, False)) * 10 ^ 3
.Range("Cw") = (WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 18, False)) * 10 ^ 9
.Range("DL_beam") = (WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 20, False))
.Range("Vr") = (WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 16, False))
.Range("Mr") = (WorksheetFunction.VLookup(i, Sheets("Database").Range("Y3:AR239"), 19, False))
End With
End If

End sub

I'm not very proficient with VBA, and I don't know what the problem is or how to fix it

Thanks for the help,
Josh

Bob Phillips
10-04-2011, 10:47 AM
Josh,

Bit hard to fathom. Where is the combo, on a form or a worksheet?

Can you post the whole workbook, give us something to work with?

JoshuaPeters
10-04-2011, 11:07 AM
Sorry about that. The Combo is on a worksheet. Do you want the Excel file, or the VBA code as an attachment, or what can i give you that would help?

Bob Phillips
10-04-2011, 03:38 PM
The Excel workbook would be best by far.