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