Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error "381" - Could not set the List Property.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error "381" - Could not set the List Property.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Unable to get Pivot Tables Property of Worksheet Class "Error | Excel Programming | |||
Hiding Rows Error - "Runtime Error 1004 - Unable to set the hidden property of the range class" | Excel Programming | |||
When Using Format(Now(), "yyyymmmddhhmm") get wrong number of arguments or invalid property assignment Error | Excel Programming | |||
cannot use "Create List" and "Share Workbook" same time | Excel Discussion (Misc queries) | |||
Run time error 1004: "No list was found.." | Excel Programming |