Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel crashes when running Macro with Automation Error

Posted on 2013-12-03
31
Medium Priority
?
2,834 Views
Last Modified: 2013-12-06
Hi,

Excel crashes when I press a button which runs a macro userform1.show OR when I use auto_open.

If I go into the vba code and run it line by line, the program works fine.

Once excel has crashed and windows recovers the file, I then get an "Automation Error" box pop up asking me to close the application.

I am using Excel 2013 on one computer and Excel 2010 on another, the issue happens on both.

Part of my code is pasted below - Please let me know if you need the full code to help identify the issue.

Sub StartPro()
Haulage.Show
End Sub

Open in new window


which calls

Private Sub UserForm_Activate()
    Me.MultiPage2.Value = 0
End Sub

Private Sub UserForm_Initialize()

TextBox1.Value = Format(Date, "dd/mm/yyyy")
Label4.Caption = Format(Sheets("PHCosts").Range("F2").Value, "Currency")
Label5.Caption = Format(Sheets("PHCosts").Range("G2").Value, "Currency")

Sheets("Haul").Select
'Registration comboboxes
With Me.ComboBox1
.RowSource = Range("A2", Sheets("Haul").Range("A2").End(xlDown)).Address
End With
With Me.ComboBox22
.RowSource = Range("A2", Sheets("Haul").Range("A2").End(xlDown)).Address
End With
With Me.ComboBox43
.RowSource = Range("A2", Sheets("Haul").Range("A2").End(xlDown)).Address
End With
With Me.ComboBox64
.RowSource = Range("A2", Sheets("Haul").Range("A2").End(xlDown)).Address
End With
With Me.ComboBox85
.RowSource = Range("A2", Sheets("Haul").Range("A2").End(xlDown)).Address
End With
With Me.ComboBox106
.RowSource = Range("A2", Sheets("Haul").Range("A2").End(xlDown)).Address
End With
With Me.ComboBox127
.RowSource = Range("A2", Sheets("Haul").Range("A2").End(xlDown)).Address
End With
With Me.ComboBox148
.RowSource = Range("A2", Sheets("Haul").Range("A2").End(xlDown)).Address
End With
With Me.ComboBox169
.RowSource = Range("A2", Sheets("Haul").Range("A2").End(xlDown)).Address
End With
With Me.ComboBox190
.RowSource = Range("A2", Sheets("Haul").Range("A2").End(xlDown)).Address
End With

'Cost Centre Comboboxes
'Wagon 1
With Me.ComboBox2
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox6
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox10
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox14
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox18
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
'Wagon 2
With Me.ComboBox23
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox27
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox31
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox35
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox39
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
'Wagon 3
With Me.ComboBox44
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox48
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox52
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox56
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox60
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
'Wagon 4
With Me.ComboBox65
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox69
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox73
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox77
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox81
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
'Wagon 5
With Me.ComboBox86
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox90
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox94
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox98
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox102
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
'Wagon 6
With Me.ComboBox107
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox111
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox115
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox119
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox123
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
'Wagon 7
With Me.ComboBox128
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox132
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox136
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox140
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox144
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
'Wagon 8
With Me.ComboBox149
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox153
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox157
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox161
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox165
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
'Wagon 9
With Me.ComboBox170
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox174
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox178
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox182
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox186
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
'Wagon 10
With Me.ComboBox191
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox195
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox199
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox203
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With
With Me.ComboBox207
.RowSource = Range("D2", Sheets("Haul").Range("D2").End(xlDown)).Address
End With

'Customer Comboboxes
'Wagon 1
With Me.ComboBox3
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox7
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox11
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox15
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox19
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
'Wagon 2
With Me.ComboBox24
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox28
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox32
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox36
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox40
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
'Wagon 3
With Me.ComboBox45
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox49
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox53
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox57
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox61
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
'Wagon 4
With Me.ComboBox66
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox70
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox74
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox78
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox82
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
'Wagon 5
With Me.ComboBox87
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox91
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox95
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox99
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox103
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
'Wagon 6
With Me.ComboBox108
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox112
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox116
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox120
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox124
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
'Wagon 7
With Me.ComboBox129
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox133
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox137
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox141
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox145
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
'Wagon 8
With Me.ComboBox150
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox154
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox158
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox162
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox166
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
'Wagon 9
With Me.ComboBox171
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox175
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox179
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox183
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox187
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
'Wagon 10
With Me.ComboBox192
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox196
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox200
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox204
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With
With Me.ComboBox208
.RowSource = Range("E2", Sheets("Haul").Range("E2").End(xlDown)).Address
End With

'Delivery Comboboxes
'Wagon 1
With Me.ComboBox4
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox8
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox12
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox16
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox20
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
'Wagon 2
With Me.ComboBox25
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox29
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox33
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox37
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox41
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
'Wagon 3
With Me.ComboBox46
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox50
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox54
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox58
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox62
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
'Wagon 4
With Me.ComboBox67
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox71
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox75
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox79
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox83
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
'Wagon 5
With Me.ComboBox88
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox92
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox96
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox100
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox104
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
'Wagon 6
With Me.ComboBox109
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox113
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox117
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox121
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox125
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
'Wagon 7
With Me.ComboBox130
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox134
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox138
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox142
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox146
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
'Wagon 8
With Me.ComboBox151
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox155
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox159
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox163
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox167
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
'Wagon 9
With Me.ComboBox172
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox176
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox180
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox184
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox188
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
'Wagon 10
With Me.ComboBox193
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox197
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox201
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox205
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With
With Me.ComboBox209
.RowSource = Range("F2", Sheets("Haul").Range("F2").End(xlDown)).Address
End With

'Delivery comboboxes
With Me.ComboBox5
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox9
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox13
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox17
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox21
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With

'Wagon 2
With Me.ComboBox26
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox30
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox34
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox38
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox42
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With

'Wagon 3
With Me.ComboBox47
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox51
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox55
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox59
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox63
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With

'Wagon 4
With Me.ComboBox68
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox72
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox76
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox80
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox84
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With

'Wagon 5
With Me.ComboBox89
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox93
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox97
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox101
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox105
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With

'Wagon 6
With Me.ComboBox110
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox114
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox118
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox122
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox126
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With

'Wagon 7
With Me.ComboBox131
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox135
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox139
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox143
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox147
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With

'Wagon 8
With Me.ComboBox152
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox156
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox160
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox164
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox168
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With

'Wagon 9
With Me.ComboBox173
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox177
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox181
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox185
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox189
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With

'Wagon 10
With Me.ComboBox194
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox198
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox202
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox206
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With
With Me.ComboBox210
.RowSource = Range("G2", Sheets("Haul").Range("G2").End(xlDown)).Address
End With

End Sub

Open in new window


Please help!

Many Thanks
0
Comment
Question by:martinibbo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 10
  • 7
31 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39692216
I think the workbook would be more use. You can remove any data from it or just censor it as need be.

Regards,
Rory
0
 

Author Comment

by:martinibbo
ID: 39692250
Hi Rory,

Thanks for your quick response, please see attached file as requested.
I have removed a lot of the sensitive data

Hope you can help.

Thanks
Sample-of-JM-Haulage-Cals.xlsm
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39692283
I can't replicate any crashes. There are some compile errors due to Option Explicit and undeclared variables (and your auto_open calls a non-existent routine so I corrected that - it should also appear after the Option Explicit.)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:andrew_man
ID: 39692329
You have problem with MS security update:

http://support.microsoft.com/kb/2664258/en-us

You can return back old version of mscomctl.ocx (unregister and register again) or try to follow this recommendations:

http://dutchgemini.wordpress.com/2012/04/17/excel-vba-error-could-not-load-an-object-because-it-is-not-available-on-this-machine/
0
 

Author Comment

by:martinibbo
ID: 39692477
Rory, the crash seems to happen if I click save, then exit / re-open then click on the button of sheets("haul"). See if this helps you replicate.

Andrew_man - thank you for your input, I will have to look at this potential solution a little later on.

Thanks
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39694827
As far as I can see you aren't using any of the Common Controls at all, so I'm not sure how that MSKB article could apply.

I'll try again to recreate the issue. One question: perhaps it's old age, but I can't see any buttons on the Haul sheet?
0
 

Author Comment

by:martinibbo
ID: 39694888
Apologies, I have restored mine from a backup a couple of times and put the button on sheets (PHCosts) in your version. All the button does is run the macro to load the userform.

It was more a button for ease of testing, rather than having to run it from the modules behind the scenes.

Strange thing is that when I wrote the program early last week, there didn't seem to be any issues, it was not until this week when I experienced the program crashing. Could it be something to do with a windows update?

Andrew_man, I don't think that this is an issue with MS Security update, unless a very recent one as it was working last week. (I think your article referenced an update in 2012) Within our network the program crashes on 2 separate windows 7 machines running office 2013, and on a windows 8.1 machine running office 2013.

?? Any ideas ??
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39695061
Try to add this two line in Private Sub ComboBox1_Change()

Dim a As String
Dim cell As Variant
0
 

Author Comment

by:martinibbo
ID: 39697972
Andrew_man, I have added in these two lines - still same result :(
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39697975
can dump the error screen?  Thanks!
0
 

Author Comment

by:martinibbo
ID: 39698012
crash screen shot Screen Dump attached - it is only on occasion that the "automation error" pops up, but it has no further details (this is once excel has restarted)

Event viewer shows Event ID 1000:
Faulting application name: EXCEL.EXE, version: 14.0.7109.5000, time stamp: 0x522a4031
Faulting module name: unknown, version: 0.0.0.0, time stamp: 0x00000000
Exception code: 0xc0000005
Fault offset: 0x00000000
Faulting process id: 0x161c
Faulting application start time: 0x01cef1b6ab2c9df0
Faulting application path: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
Faulting module path: unknown
Report Id: f1f70885-5da9-11e3-8f27-dc0ea14d1ee7

I have recreated this on our office laptop using the file attached on here Office 2010. (this laptop is not connected to our network)

hmmmm
0
 

Author Comment

by:martinibbo
ID: 39698018
The exact steps that I take to cause the crash is as follows:

1) open file and press button
2) exit userform
3) press save
4) exit application
5) re-launch application
6) press button - This is where the crash happens

Thanks
0
 

Author Comment

by:martinibbo
ID: 39698081
automation error
0
 

Author Comment

by:martinibbo
ID: 39698160
Ok, so I have ruled out the MSCOMCTL.OCX, I ran the following from an elevated command prompt:

Regsvr32 /u c:\windows\SysWOW64\MSCOMCTL.OCX
Regsvr32 c:\windows\SysWOW64\MSCOMCTL.OCX

Didn't change a thing though....

Perhaps its a different OCX / DLL??

Thanks
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698166
Good Try!  I have mentioned two days ago!

by: andrew_manPosted on 2013-12-03 at 20:58:19ID: 39692329

You have problem with MS security update:

http://support.microsoft.com/kb/2664258/en-us

You can return back old version of mscomctl.ocx (unregister and register again) or try to follow this recommendations:

http://dutchgemini.wordpress.com/2012/04/17/excel-vba-error-could-not-load-an-object-because-it-is-not-available-on-this-machine/
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698171
By the way, your coding is missing a lot of checking point.  If you have time, please add it.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39698191
I still cannot replicate the crash, which makes life tricky in debugging, but I will try rewriting the code for you to test.
0
 

Author Comment

by:martinibbo
ID: 39698205
Andrew_man - I am fairly new to VBA, and have taught myself up to now. Can you elaborate on what you mean please?

Rorya - many thanks for your efforts

I appreciate both of your help.

Thanks
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698215
Have you solve your crash problem?

Just take an example

ActiveCell.Offset(0, 15).Value = TextBox8.Value - ActiveCell.Offset(0, 14).Value

If the user no input in the TextBox8.Value, the program will break here.

You should to add more checkpoint in the VBA

Keep in touch!
0
 

Author Comment

by:martinibbo
ID: 39698219
No not solved yet.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39698247
Success - I have got it to crash! :)

Now to see if I can figure out why...
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39698278
Before I contemplate reworking all that code, could you just quickly confirm if this version still crashes for you?
Sample-of-JM-Haulage-Cals-1-.xlsm
0
 

Author Comment

by:martinibbo
ID: 39698295
Yes unfortunately it still crashes. :(
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39698305
too bad!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39698384
OK, I will take another look when I have a chance.
0
 

Author Comment

by:martinibbo
ID: 39698413
Thanks
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 39698678
How about this one. I've started streamlining the code in case it was the quantity of code that was at issue.
Sample-of-JM-Haulage-Cals-RA-EE.xlsm
0
 

Author Comment

by:martinibbo
ID: 39698888
Rory - I cannot recreate the crash so far.
I will try to make it crash again tomorrow, but so far looks great!!

What do you think the specific cause was? Is it just volume in the way that I had written it?

I will report back tomorrow.

Thank you very much!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39699486
I didn't check the size of the module but there was a lot of code there so you could have been hitting the limit.
0
 

Author Comment

by:martinibbo
ID: 39700370
Brilliant Rory! Works a treat!

I owe you one :)

Many Thanks for you efforts
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39700403
Glad to help. :)
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question