I have created a user form that collects information about a job then once the ‘Submit Record’ button is pressed adds the information to a table within the workbook.
Within the user form are rows of combo boxes that are filled in from left to right and the selections made in the first 2 combo boxes then tells the user form how to populate the 3rd combo box. (The 3rd combo box is populated from different ranges within another sheet of the workbook and has 3 columns)
Pic of user form row of combo boxes Below
Screen Shot 2012-05-19 at 12.58.09.png
To the right of each row is a ‘reset button’ which clears all combo boxes in the row. This allows the user to re-enter data but might be causing problems when I try to submit the form with the combo boxes blank after the reset button has been pressed.
Code below;
Private Sub ResetMach1_Click()
Me.MCompanyCBO1.Value = ""
Me.MSectionComboBox1.Value = ""
For i = 1 To Me.MachineCBO1.ListCount
Me.MachineCBO1.RemoveItem 0
Next i
Me.MachineQuantityCBO1.Value = ""
Me.MachineHoursCBO1.Value = 0
Me.M1C1TextBox.Value = ""
Me.M1ActProTextBox.Value = ""
Call CalculateMachineAndActivityProductionTotals
Me.MCompanyCBO1.SetFocus
End Sub
My problem comes when I try to ‘Submit Record’. I was using the below code to add the values from the combo boxes to my table;
Cells(emptyRow, 28).Value = Me.MachineCBO1.Column(0)
Cells(emptyRow, 29).Value = Me.MachineCBO1.Column(1)
Cells(emptyRow, 30).Value = Me.MachineCBO1.Column(2)
Cells(emptyRow, 31).Value = Me.MachineQuantityCBO1
Cells(emptyRow, 32).Value = Me.MachineHoursCBO1
Which works fine if the combo boxes are populated or untouched by the user but if the combo boxes were selected then the ‘reset’ button was pressed to clear the boxes then the user form was submitted I get the run time error 381 “Could not get the Column property. Invalid property array index”
(this is my first post so if I have not explained something properly or more code/ pics are needed then please just ask)
Hope someone can help…
Thank-you in anticipation
Ben
Bookmarks