Hello,
I've got a problem with some cascading list boxes and a single drop down. There are 4 list boxes and 1 drop down and a user selects options from each of them to make a configuration. They are embedded in the worksheet "Instructions" (not a Userform). I encounter an object required error 424 at two lines of VBA code and a run-time error 1004 at two other lines of VBA code (please see below).
Here's the code in its entirety. The error lines are in bold. The next code box will points out which lines have which errors.
Here's the section of code that has the errors with the error lines in bold.
In the code, the lines that have "ActiveWorkbook.Names.Add Name", for example:
are used along with the If-Then-ElseIf statements to apply dynamic ranges to the cascading list boxes and drop down. For this to work though I need to set the Input Range of the list box or drop down on the "Instructions" worksheet to "CoaxSizeList" or "GSSGList" or "BodyList", etc.
The Object required error 424 occurs when I select different options in the list boxes. I think it's odd that the error doesn't occur on an earlier line of code that uses the same functions. For example:
The first Object required error 424 occurs on this line:
but not these lines which are earlier in the Sub:
I can answer any questions you have if you're interested in helping me out.
Thank you
-Nick
I've got a problem with some cascading list boxes and a single drop down. There are 4 list boxes and 1 drop down and a user selects options from each of them to make a configuration. They are embedded in the worksheet "Instructions" (not a Userform). I encounter an object required error 424 at two lines of VBA code and a run-time error 1004 at two other lines of VBA code (please see below).
Here's the code in its entirety. The error lines are in bold. The next code box will points out which lines have which errors.
Code:
Sub ProbeSelect()
Instructions.Activate
ProbeSpec.Activate
'All pictures in the worksheet are initially hidden. They are organized in the following code like so:
'1st Line: Single Coax probes without a Mini Shelf (i40/i50/i67 and i40-SC/i50-SC/i67-SC)
'2nd Line: Single Coax probes with a Mini Shelf (i110 and i110-SC)
'3rd Line: Dual Coax probes without Mini Shelf and without GSSG (i40/i50/i67 Dual, Standard and i40-SC/i50-SC/i67-SC Dual, Standard)
'4th Line: Dual Coax probes with a Mini Shelf and without GSSG (i110 Dual, Standard and i110-SC Dual, Standard)
'5th Line: Dual Coax probes without Mini Shelf and with GSSG (i40/i50/i67 Dual, GSSG and i40-SC/i50-SC/i67-SC Dual, GSSG)
'6th Line: Dual Coax probes with a Mini Shelf and with GSSG (i110 Dual, GSSG and i110-SC Dual, GSSG)
'7th Line: A text box with a note to users cutting the Mini Shelf. *Consider eliminating this tex box and adding the note to the instructions.
Instructions.Shapes.Range(Array("Single_Fig1", "Single_Fig2", "Single_Fig3", "Single_Fig4", "Single_Fig5", "Single_Fig6", "Single_Fig7", "Single_Fig8", _
"SingleMini_Fig5", "SingleMini_Fig6", "SingleMini_Fig7", "SingleMini_Fig8", _
"Dual_Fig1", "Dual_Fig2", "Dual_Fig3", "Dual_Fig4", "Dual_Fig5", "Dual_Fig6", "Dual_Fig7", "Dual_Fig8", _
"DualMini_Fig5", "DualMini_Fig6", "DualMini_Fig7", "DualMini_Fig8", _
"DualGSSG_Fig7", "DualGSSG_Fig8", "DualGSSG_Fig9", _
"DualMiniGSSG_Fig7", "DualMiniGSSG_Fig8", "DualMiniGSSG_Fig9", _
"MiniShelfAlert")).Visible = False
Instructions.Rows("12:12").Hidden = False 'shows warning sign (hidden in 'parameters')
Instructions.Shapes("HitRefresh").Visible = True
'''''''''''''''''''''''''''''''''
'''''''''''Coax Config'''''''''''
'''''''''''''''''''''''''''''''''
Dim CCLB As ListBox
Set CCLB = Instructions.ListBoxes("CoaxConfigListBox")
Set r = ProbeSpec.Range(CCLB.ListFillRange)
Set CCLBValue = r(CCLB.Value)
If CCLBValue = "Single" Then
Dim CSRange As Range
Set CSRange = ProbeSpec.Range("CoaxSize")
ActiveWorkbook.Names.Add Name:="CoaxSizeList", RefersTo:=CSRange
Dim GSSGRange As Range
Set GSSGRange = ProbeSpec.Range("Empty")
ActiveWorkbook.Names.Add Name:="GSSGList", RefersTo:=GSSGRange
ElseIf CCLBValue = "Dual" Then
Dim CSDualRange As Range
Set CSDualRange = ProbeSpec.Range("CoaxSize")
ActiveWorkbook.Names.Add Name:="CoaxSizeList", RefersTo:=CSDualRange
Dim GSSGDualRange As Range
Set GSSGDualRange = ProbeSpec.Range("GSSG")
ActiveWorkbook.Names.Add Name:="GSSGList", RefersTo:=GSSGDualRange
Dim BodyDualRange1 As Range
Set BodyDualRange1 = ProbeSpec.Range("Empty")
ActiveWorkbook.Names.Add Name:="BodyList", RefersTo:=BodyDualRange1
End If
Instructions.Range("CoaxConfigOutput").Value = CCLBValue
'''''''''''''''''''''''''''''''
'''''''''''Coax Size'''''''''''
'''''''''''''''''''''''''''''''
Dim CSLB As ListBox
Set CSLB = Instructions.ListBoxes("CoaxSizeListBox")
Set r = Worksheets("ProbeSpec").Range(CSLB.ListFillRange)
Set CSLBValue = r(CSLB.Value)
If CSLBValue = "0.031" And CCLBValue = "Single" Then
Dim BodyRange1 As Range
Set BodyRange1 = ProbeSpec.Range("Body")
ActiveWorkbook.Names.Add Name:="BodyList", RefersTo:=BodyRange1
ElseIf CSLBValue = "0.023" Then
Dim BodyRange2 As Range
Set BodyRange2 = ProbeSpec.Range("Empty")
ActiveWorkbook.Names.Add Name:="BodyList", RefersTo:=BodyRange2
End If
Instructions.Range("CoaxSizeOutput").Value = CSLBValue
''''''''''''''''''''''''''''''''
''''''''''''''GSSG''''''''''''''
''''''''''''''''''''''''''''''''
Dim GSSGLB As ListBox
Set GSSGLB = Instructions.ListBoxes("GSSGListBox")
Set r = ProbeSpec.Range(GSSGLB.ListFillRange)
Set GSSGLBValue = r(GSSGLB.Value)
Instructions.Range("GSSGOutput").Value = GSSGLBValue
''''''''''''''''''''''''''''''''
''''''''''''''Body''''''''''''''
''''''''''''''''''''''''''''''''
Dim BodyLB As ListBox
Set BodyLB = Instructions.ListBoxes("BodyListBox")
[SIZE=3][B]Set r = ProbSpec.Range(BodyLB.ListFillRange)[/B][/SIZE]
Set BodyLBValue = r(BodyLB.Value)
Instructions.Range("BodyOutput").Value = BodyLBValue
''''''''''''''''''''''''''''''''
'''''''''''''Model''''''''''''''
''''''''''''''''''''''''''''''''
Dim Model As DropDown
Set Model = Instructions.DropDowns("ModelDropDown")
[SIZE=3][B]Set r = ProbSpec.Range(Model.ListFillRange)[/B][/SIZE]
Set ModelValue = r(Model.Value)
Instructions.Range("ModelOutput").Value = ModelValue
If CSValue = "0.031" Then
Dim ModelRange1 As Range
Set ModelRange1 = ProbeSpec.Range("ModelStandard")
[SIZE=3][B] ActiveWorkbook.Names.Add Name:="ModelDropDown", RefersTo:=ModelRange1[/B][/SIZE]
ElseIf CSValue = "0.023" Then
Dim ModelRange2 As Range
Set RngModel2 = ProbeSpec.Range("ModelSC")
[SIZE=3][B]ActiveWorkbook.Names.Add Name:="ModelDropDown", RefersTo:=ModelRange2[/B][/SIZE]
End If
End SubHere's the section of code that has the errors with the error lines in bold.
Code:
''''''''''''''''''''''''''''''''''''''''''''''Body''''''''''''''
''''''''''''''''''''''''''''''''
Dim BodyLB As ListBox
Set BodyLB = Instructions.ListBoxes("BodyListBox")
[B]Set r = ProbSpec.Range(BodyLB.ListFillRange) '<--Object required error 424.[/B]
Set BodyLBValue = r(BodyLB.Value)
Instructions.Range("BodyOutput").Value = BodyLBValue
''''''''''''''''''''''''''''''''
'''''''''''''Model''''''''''''''
''''''''''''''''''''''''''''''''
Dim Model As DropDown
Set Model = Instructions.DropDowns("ModelDropDown")
[B]Set r = ProbSpec.Range(Model.ListFillRange) '<--Object required error 424.[/B]
Set ModelValue = r(Model.Value)
Instructions.Range("ModelOutput").Value = ModelValue
If CSValue = "0.031" Then
Dim ModelRange1 As Range
Set ModelRange1 = ProbeSpec.Range("ModelStandard")
[B]ActiveWorkbook.Names.Add Name:="ModelDropDown", RefersTo:=ModelRange1 '<--Run-time error 1004.[/B]
ElseIf CSValue = "0.023" Then
Dim ModelRange2 As Range
Set RngModel2 = ProbeSpec.Range("ModelSC")
[B]ActiveWorkbook.Names.Add Name:="ModelDropDown", RefersTo:=ModelRange2 '<--Run-time error 1004.[/B]
End If
End SubIn the code, the lines that have "ActiveWorkbook.Names.Add Name", for example:
Code:
ActiveWorkbook.Names.Add Name:="CoaxSizeList", RefersTo:=CSRangeare used along with the If-Then-ElseIf statements to apply dynamic ranges to the cascading list boxes and drop down. For this to work though I need to set the Input Range of the list box or drop down on the "Instructions" worksheet to "CoaxSizeList" or "GSSGList" or "BodyList", etc.
The Object required error 424 occurs when I select different options in the list boxes. I think it's odd that the error doesn't occur on an earlier line of code that uses the same functions. For example:
The first Object required error 424 occurs on this line:
Code:
Set r = ProbSpec.Range(BodyLB.ListFillRange)but not these lines which are earlier in the Sub:
Code:
Set r = ProbeSpec.Range(CCLB.ListFillRange)
.
.
.
Set r = Worksheets("ProbeSpec").Range(CSLB.ListFillRange)
.
.
.
Set r = ProbeSpec.Range(GSSGLB.ListFillRange)I can answer any questions you have if you're interested in helping me out.
Thank you
-Nick
Last edited:
