i figured out how to make the list storage smaller.
--
Learning


"tracktraining" wrote:

HI All,

Please help. When I have the initial data and then run the script, the new
value gets added to sheet2 correct without error. Then when I run the script
again, I can't get Invest_Results userform to open - keep getting the
following error: "Run-Time error '381': could not set the List Property.
Invalid Property array index.

What am I doing wrong? Below is the copy of my code:

Dim ComplaintRng As Range
Dim StateRng As Range
Dim ErrorRng As Range
Dim ConditionRng As Range
Dim CauseRng As Range
Dim ReplacedRng As Range
Dim AssemblyRng As Range

Option Explicit
'------
'Put information into the cells
Private Sub Submit_button_Click()
Dim InvestStr As String

InvestStr = Me.Complaint_verified + ", " + Me.Failure_state + ", " +
Me.Error_code + ", "
InvestStr = InvestStr + Me.Failure_Condition + ", " + Me.Root_cause + ",
" + Me.Assembly_PN
Stellar_Resolver.Corrections.Value = InvestStr
Stellar_Resolver.Parts_Replaced.Value = Me.PN_Replaced

'ActiveCell = Me.PreviewText
'ActiveCell.Offset(0, 1) = Me.PN_Replaced

'if the entry value is not part of list, add to list
With Worksheets("sheet2")
Set ComplaintRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
Set StateRng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
Set ErrorRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
Set ConditionRng = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
Set CauseRng = .Range("E2", .Cells(.Rows.Count, "E").End(xlUp))
Set ReplacedRng = .Range("F2", .Cells(.Rows.Count, "F").End(xlUp))
Set AssemblyRng = .Range("G2", .Cells(.Rows.Count, "G").End(xlUp))
End With

If WorksheetFunction.CountIf(ComplaintRng, Me.Complaint_verified.Value)
= 0 Then
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) =
Me.Complaint_verified.Value
End If

If WorksheetFunction.CountIf(StateRng, Me.Failure_state.Value) = 0 Then
Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) =
Me.Failure_state.Value
End If

If WorksheetFunction.CountIf(ErrorRng, Me.Error_code.Value) = 0 Then
Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) =
Me.Error_code.Value
End If

If WorksheetFunction.CountIf(ConditionRng, Me.Failure_Condition.Value) =
0 Then
Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) =
Me.Failure_Condition.Value
End If

If WorksheetFunction.CountIf(CauseRng, Me.Root_cause.Value) = 0 Then
Sheets("Sheet2").Cells(Rows.Count, 5).End(xlUp).Offset(1, 0) =
Me.Root_cause.Value
End If

If WorksheetFunction.CountIf(ReplacedRng, Me.PN_Replaced.Value) = 0 Then
Sheets("Sheet2").Cells(Rows.Count, 6).End(xlUp).Offset(1, 0) =
Me.PN_Replaced.Value
End If

If WorksheetFunction.CountIf(AssemblyRng, Me.Assembly_PN.Value) = 0 Then
Sheets("Sheet2").Cells(Rows.Count, 7).End(xlUp).Offset(1, 0) =
Me.Assembly_PN.Value
End If

Unload Me
End Sub

'-------------
'Getting the list from sheet2 to populate drop-down menus
Private Sub UserForm_Initialize()

With Worksheets("Sheet2")
Set ComplaintRng = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
Set StateRng = .Range("B2", .Cells(.Rows.Count, 2).End(xlUp))
Set ErrorRng = .Range("C2", .Cells(.Rows.Count, 3).End(xlUp))
Set ConditionRng = .Range("D2", .Cells(.Rows.Count, 4).End(xlUp))
Set CauseRng = .Range("E2", .Cells(.Rows.Count, 5).End(xlUp))
Set ReplacedRng = .Range("F2", .Cells(.Rows.Count, 6).End(xlUp))
Set AssemblyRng = .Range("G2", .Cells(.Rows.Count, 7).End(xlUp))
End With

'using the entire range of values in the column A starting with A2
Me.Complaint_verified.List = ComplaintRng.Value
Me.Failure_state.List = StateRng.Value
Me.Error_code.List = ErrorRng.Value
Me.Failure_Condition.List = ConditionRng.Value
Me.Root_cause.List = CauseRng.Value
Me.PN_Replaced.List = ReplacedRng.Value
Me.Assembly_PN.List = AssemblyRng.Value

End Sub

thanks so much!
--
Learning