Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3036
  • Last Modified:

Excel crashes when running Macro with Automation Error

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
martinibbo
Asked:
martinibbo
  • 14
  • 10
  • 7
1 Solution
 
Rory ArchibaldCommented:
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
 
martinibboAuthor Commented:
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
 
Rory ArchibaldCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
andrew_manCommented:
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
 
martinibboAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
martinibboAuthor Commented:
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
 
andrew_manCommented:
Try to add this two line in Private Sub ComboBox1_Change()

Dim a As String
Dim cell As Variant
0
 
martinibboAuthor Commented:
Andrew_man, I have added in these two lines - still same result :(
0
 
andrew_manCommented:
can dump the error screen?  Thanks!
0
 
martinibboAuthor Commented:
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
 
martinibboAuthor Commented:
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
 
martinibboAuthor Commented:
automation error
0
 
martinibboAuthor Commented:
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
 
andrew_manCommented:
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
 
andrew_manCommented:
By the way, your coding is missing a lot of checking point.  If you have time, please add it.
0
 
Rory ArchibaldCommented:
I still cannot replicate the crash, which makes life tricky in debugging, but I will try rewriting the code for you to test.
0
 
martinibboAuthor Commented:
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
 
andrew_manCommented:
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
 
martinibboAuthor Commented:
No not solved yet.
0
 
Rory ArchibaldCommented:
Success - I have got it to crash! :)

Now to see if I can figure out why...
0
 
Rory ArchibaldCommented:
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
 
martinibboAuthor Commented:
Yes unfortunately it still crashes. :(
0
 
andrew_manCommented:
too bad!
0
 
Rory ArchibaldCommented:
OK, I will take another look when I have a chance.
0
 
martinibboAuthor Commented:
Thanks
0
 
Rory ArchibaldCommented:
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
 
martinibboAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
martinibboAuthor Commented:
Brilliant Rory! Works a treat!

I owe you one :)

Many Thanks for you efforts
0
 
Rory ArchibaldCommented:
Glad to help. :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 14
  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now