Solved

Excel crashes when running Macro with Automation Error

Posted on 2013-12-03
31
2,708 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

617 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