Solved

Excel crashes when running Macro with Automation Error

Posted on 2013-12-03
31
2,299 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
  • 14
  • 10
  • 7
31 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 4

Expert Comment

by:andrew_man
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Try to add this two line in Private Sub ComboBox1_Change()

Dim a As String
Dim cell As Variant
0
 

Author Comment

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

Expert Comment

by:andrew_man
Comment Utility
can dump the error screen?  Thanks!
0
 

Author Comment

by:martinibbo
Comment Utility
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
Comment Utility
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
Comment Utility
automation error
0
 

Author Comment

by:martinibbo
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:andrew_man
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No not solved yet.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Success - I have got it to crash! :)

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

Expert Comment

by:Rory Archibald
Comment Utility
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
Comment Utility
Yes unfortunately it still crashes. :(
0
 
LVL 4

Expert Comment

by:andrew_man
Comment Utility
too bad!
0
 
LVL 85

Expert Comment

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

Author Comment

by:martinibbo
Comment Utility
Thanks
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Brilliant Rory! Works a treat!

I owe you one :)

Many Thanks for you efforts
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Glad to help. :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now