Form Control List Boxes Cascading - Errors 424 and 1004

nryan

Board Regular
Joined
Apr 3, 2015
Messages
56
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.

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 Sub

Here'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 Sub
In the code, the lines that have "ActiveWorkbook.Names.Add Name", for example:

Code:
ActiveWorkbook.Names.Add Name:="CoaxSizeList", RefersTo:=CSRange
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:

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:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
I don't fully appreciate everything your doing, but here's one observation on the first error.

It appears you only set the Named Range for BodyListBox when "Dual" is selected.
Code:
[COLOR=darkblue]If[/COLOR] CCLBValue = "Single" [COLOR=darkblue]Then[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] CSRange [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Set[/COLOR] CSRange = ProbeSpec.Range("CoaxSize")
    ActiveWorkbook.Names.Add Name:="CoaxSizeList", RefersTo:=CSRange
    
    [COLOR=darkblue]Dim[/COLOR] GSSGRange [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Set[/COLOR] GSSGRange = ProbeSpec.Range("Empty")
    ActiveWorkbook.Names.Add Name:="GSSGList", RefersTo:=GSSGRange
    
[COLOR=darkblue]ElseIf[/COLOR] CCLBValue = "Dual" [COLOR=darkblue]Then[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] CSDualRange [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Set[/COLOR] CSDualRange = ProbeSpec.Range("CoaxSize")
    ActiveWorkbook.Names.Add Name:="CoaxSizeList", RefersTo:=CSDualRange
    
    [COLOR=darkblue]Dim[/COLOR] GSSGDualRange [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Set[/COLOR] GSSGDualRange = ProbeSpec.Range("GSSG")
    ActiveWorkbook.Names.Add Name:="GSSGList", RefersTo:=GSSGDualRange
    
    [COLOR=darkblue]Dim[/COLOR] BodyDualRange1 [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Set[/COLOR] BodyDualRange1 = ProbeSpec.Range("Empty")
    ActiveWorkbook.Names.Add Name:="BodyList", RefersTo:=BodyDualRange1
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
But you still run the BodyListBox code later even when you select "Single". Could it be it errors when you select "Single" because BodyListBox doesn't have a valid named range?



On another note (this doesn't address your errors), you could replace this...
Code:
[COLOR=darkblue]Dim[/COLOR] CCLB [COLOR=darkblue]As[/COLOR] ListBox
[COLOR=darkblue]Set[/COLOR] CCLB = Instructions.ListBoxes("CoaxConfigListBox")
[COLOR=darkblue]Set[/COLOR] r = ProbeSpec.Range(CCLB.ListFillRange)
[COLOR=darkblue]Set[/COLOR] CCLBValue = r(CCLB.Value)
With something like this...
Code:
[COLOR=green]'CoaxConfigListBox selected value[/COLOR]
[COLOR=darkblue]With[/COLOR] Instructions.ListBoxes("CoaxConfigListBox")
    CCLBValue = .Parent.Range(.ListFillRange)(.Value)
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
Last edited:

nryan

Board Regular
Joined
Apr 3, 2015
Messages
56
Hi AlphaFrog,

Thanks for your honesty. I don't appreciate how this code was written either. I'm trying to improve something that was written by an intern a while back. The changes I've made have caused errors and I'm trying to fix them. I don't have experience with VBA other than this project and I'm trying not to go down that road of rewriting the whole thing. I'll try out your suggestions.

Thank you.
 

nryan

Board Regular
Joined
Apr 3, 2015
Messages
56
It appears you only set the Named Range for BodyListBox when "Dual" is selected.
I set the Named Range for "Single" but that didn't seem to make a difference. The same error occurs on the same line of code. For your next suggestion:

On another note (this doesn't address your errors), you could replace this...
Code:
[COLOR=darkblue]Dim[/COLOR] CCLB [COLOR=darkblue]As[/COLOR] ListBox
[COLOR=darkblue]Set[/COLOR] CCLB = Instructions.ListBoxes("CoaxConfigListBox")
[COLOR=darkblue]Set[/COLOR] r = ProbeSpec.Range(CCLB.ListFillRange)
[COLOR=darkblue]Set[/COLOR] CCLBValue = r(CCLB.Value)
With something like this...
Code:
[COLOR=green]'CoaxConfigListBox selected value[/COLOR]
[COLOR=darkblue]With[/COLOR] Instructions.ListBoxes("CoaxConfigListBox")
    CCLBValue = .Parent.Range(.ListFillRange)(.Value)
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
I tried this initially for the Coax Config section and deactivated my code with apostrophes:

Code:
'''''''''''''''''''''''''''''''''
'''''''''''Coax Config'''''''''''
'''''''''''''''''''''''''''''''''


With Instructions.ListBoxes("CoaxConfigListBox")
    CCLBValue = .Parent.Range(.ListFillRange)(.Value)
End With


'Dim CCLB As ListBox
'Set CCLB = Instructions.ListBoxes("CoaxConfigListBox")
'Set r = ProbeSpec.Range(CCLB.ListFillRange)


'Set CCLBValue = r(CCLB.Value)
But that resulted in a Run-time error 1004 on the second line "CCLBValue = .Parent.Range(.ListFillRange)(.Value)". However, I'd like to use this instead if I can make it work. Any suggestions?

Thank you.
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
I missed that the lists were on another sheet. Replace .Parent with ProbeSpec
Code:
With Instructions.ListBoxes("CoaxConfigListBox")
    CCLBValue = [B]ProbeSpec[/B].Range(.ListFillRange)(.Value)
End With
This will test if any item is selected before trying to get the selected item from the list.
Code:
[COLOR=darkblue]With[/COLOR] Instructions.ListBoxes("BodyListBox")
    [COLOR=darkblue]If[/COLOR] .Value > 0 [COLOR=darkblue]Then[/COLOR]
        BodyLBValue = ProbSpec.Range(.ListFillRange)(.Value)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]With[/COLOR]
 

nryan

Board Regular
Joined
Apr 3, 2015
Messages
56
You're suggestions are working AlphaFrog. Thanks for the help so far. It's been huge!

I've updated my code and posted it below. It doesn't have errors now, but when I make a selection from any of the list boxes the "ProbeSpec" worksheet pops up and I have to click the tab to take me back to the "Instructions" worksheet that contains the list boxes.

I've reordered the cascading list boxes differently. Now they're ordered like so:

1. Body - Angle, Vertical, Wave Guide
2. Coax Config - Single, Dual
3. GSSG - Yes, No
4. Coax Size - 0.031, 0.023
5. Model - StandardGroup, SCGroup, WGGroup

The items after the hyphen "-" are the options for each list box. There aren't many unique combinations. Usually a selection in say Body or GSSG will just render subsequent boxes as "Empty".

Also, the list boxes aren't updating sometimes because a downstream option is selected that isn't supposed to be available when a certain upstream option is selected. I guess this means the list boxes aren't updating.

How to I:

1. keep the active worksheet from changing to "ProbeSpec" when I make selections in the list boxes (which are on the "Instructions" worksheet),
2. force the list boxes to update when a selection is made, and
3. make default selections (before the user makes a selection). Optional

Thanks.

Code:
Sub ProbeSelect2()

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


''''''''''''''''''''''''''''''''
''''''''''''''Body''''''''''''''
''''''''''''''''''''''''''''''''


With Instructions.ListBoxes("BodyListBox")
    BodyLBValue = ProbeSpec.Range(.ListFillRange)(.Value)  'BodyListBox Value
End With


If BodyLBValue = "Angle" Then
    
    Dim CCAngleRange As Range
    Set CCAngleRange = ProbeSpec.Range("CoaxConfig")
    ActiveWorkbook.Names.Add Name:="CoaxConfigList", RefersTo:=CCAngleRange


ElseIf BodyLBValue = "Vertical" Then
    
    Dim CCVerticalRange As Range
    Set CCVerticalRange = ProbeSpec.Range("CoaxConfig")
    ActiveWorkbook.Names.Add Name:="CoaxConfigList", RefersTo:=CCVerticalRange


    
ElseIf BodyLBValue = "Wave Guide" Then
    
    Dim CCWGRange As Range
    Set CCWGRange = ProbeSpec.Range("Empty")
    ActiveWorkbook.Names.Add Name:="CoaxConfigList", RefersTo:=CCWGRange
    
    Dim CSRange As Range
    Set CSRange = ProbeSpec.Range("Empty")
    ActiveWorkbook.Names.Add Name:="CoaxSizeList", RefersTo:=CSRange
    
    Dim ModelWGRange As Range
    Set ModelWGRange = ProbeSpec.Range("ModelWG")
    ActiveWorkbook.Names.Add Name:="ModelList", RefersTo:=ModelWGRange


End If


Instructions.Range("BodyOutput").Value = BodyLBValue


''''''''''''''''''''''''''''''''
'''''''''''Coax Config''''''''''
''''''''''''''''''''''''''''''''


With Instructions.ListBoxes("CoaxConfigListBox")
    CCLBValue = ProbeSpec.Range(.ListFillRange)(.Value)  'CoaxConfigListBox Value
End With


If CCLBValue = "Single" Then
    
    Dim GSSGNARange As Range
    Set GSSGNARange = ProbeSpec.Range("Empty")
    ActiveWorkbook.Names.Add Name:="GSSGList", RefersTo:=GSSGNARange


ElseIf CCLBValue = "Dual" Then
    
    Dim GSSGRange As Range
    Set GSSGRange = ProbeSpec.Range("GSSG")
    ActiveWorkbook.Names.Add Name:="GSSGList", RefersTo:=GSSGRange


End If


Instructions.Range("CoaxConfigOutput").Value = CCLBValue


''''''''''''''''''''''''''''''''
''''''''''''''GSSG''''''''''''''
''''''''''''''''''''''''''''''''


With Instructions.ListBoxes("GSSGListBox")
    GSSGLBValue = ProbeSpec.Range(.ListFillRange)(.Value)  'GSSGListBox Value
End With


''''''''''''''''''''''''''''''''
''''''''''''Coax Size'''''''''''
''''''''''''''''''''''''''''''''


With Instructions.ListBoxes("CoaxSizeListBox")
    CSLBValue = ProbeSpec.Range(.ListFillRange)(.Value)  'CoaxSizeListBox Value
End With


If CSLBValue = "0.031" Then
    
    Dim ModelStandardRange As Range
    Set ModelStandardRange = ProbeSpec.Range("ModelStandard")
    ActiveWorkbook.Names.Add Name:="ModelList", RefersTo:=ModelStandardRange


ElseIf CSLBValue = "0.023" Then
    
    Dim ModelSCRange As Range
    Set ModelSCRange = ProbeSpec.Range("ModelSC")
    ActiveWorkbook.Names.Add Name:="ModelList", RefersTo:=ModelSCRange


End If


Instructions.Range("CoaxSizeOutput").Value = CSLBValue


''''''''''''''''''''''''''''''''
''''''''''''''Model'''''''''''''
''''''''''''''''''''''''''''''''


With Instructions.ListBoxes("ModelListBox")
    ModelLBValue = ProbeSpec.Range(.ListFillRange)(.Value)  'ModelListBox Value
End With


Instructions.Range("ModelOutput").Value = ModelLBValue


End Sub
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
You'll no doubt have to tweak this further.

Code:
[COLOR=darkblue]Sub[/COLOR] ProbeSelect2()
        
    [COLOR=green]'Instructions.Activate[/COLOR]
    [COLOR=green]'ProbeSpec.Activate[/COLOR]
    
    [COLOR=green]'All pictures in the worksheet are initially hidden. They are organized in the following code like so:[/COLOR]
        [COLOR=green]'1st Line: Single Coax probes without a Mini Shelf (i40/i50/i67 and i40-SC/i50-SC/i67-SC)[/COLOR]
        [COLOR=green]'2nd Line: Single Coax probes with a Mini Shelf (i110 and i110-SC)[/COLOR]
        [COLOR=green]'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)[/COLOR]
        [COLOR=green]'4th Line: Dual Coax probes with a Mini Shelf and without GSSG (i110 Dual, Standard and i110-SC Dual, Standard)[/COLOR]
        [COLOR=green]'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)[/COLOR]
        [COLOR=green]'6th Line: Dual Coax probes with a Mini Shelf and with GSSG (i110 Dual, GSSG and i110-SC Dual, GSSG)[/COLOR]
        [COLOR=green]'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.[/COLOR]
    
    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 = [COLOR=darkblue]False[/COLOR]
    
    Instructions.Rows("12:12").Hidden = [COLOR=darkblue]False[/COLOR]  [COLOR=green]'shows warning sign (hidden in 'parameters')[/COLOR]
    Instructions.Shapes("HitRefresh").Visible = [COLOR=darkblue]True[/COLOR]
    
    
    [COLOR=darkblue]Dim[/COLOR] lbBody  [COLOR=darkblue]As[/COLOR] ListBox: [COLOR=darkblue]Set[/COLOR] lbBody = Instructions.ListBoxes("BodyListBox")
    [COLOR=darkblue]Dim[/COLOR] lbCoax  [COLOR=darkblue]As[/COLOR] ListBox: [COLOR=darkblue]Set[/COLOR] lbCoax = Instructions.ListBoxes("CoaxConfigListBox")
    [COLOR=darkblue]Dim[/COLOR] lbGSSG  [COLOR=darkblue]As[/COLOR] ListBox: [COLOR=darkblue]Set[/COLOR] lbGSSG = Instructions.ListBoxes("GSSGListBox")
    [COLOR=darkblue]Dim[/COLOR] lbSize  [COLOR=darkblue]As[/COLOR] ListBox: [COLOR=darkblue]Set[/COLOR] lbSize = Instructions.ListBoxes("CoaxSizeListBox")
    [COLOR=darkblue]Dim[/COLOR] lbModel [COLOR=darkblue]As[/COLOR] ListBox: [COLOR=darkblue]Set[/COLOR] lbModel = Instructions.ListBoxes("ModelListBox")
    
    
    [COLOR=green]'Define lists and Reset dependent listboxes[/COLOR]
    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] Application.Caller  [COLOR=green]'Name of the listbox that called the procedure[/COLOR]
    
        [COLOR=darkblue]Case[/COLOR] "BodyListBox"  [COLOR=green]'Body[/COLOR]
        
            [COLOR=green]'Body Output[/COLOR]
            Instructions.Range("BodyOutput").Value = ProbeSpec.Range(lbBody.ListFillRange)(lbBody.Value)  [COLOR=green]'BodyListBox Value[/COLOR]
                    
            [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] lbBody.Value
                [COLOR=darkblue]Case[/COLOR] 1, 2 [COLOR=green]'Angle, Vertical[/COLOR]
                    ActiveWorkbook.Names.Add Name:="CoaxConfigList", RefersTo:=ProbeSpec.Range("CoaxConfig")
                    lbCoax.Value = 0    [COLOR=green]'Deselect[/COLOR]
                    Instructions.Range("CoaxSizeOutput").Value = ProbeSpec.Range(lbCoax.ListFillRange)(lbCoax.Value)  [COLOR=green]'CoaxSize Value[/COLOR]
                [COLOR=darkblue]Case[/COLOR] 3 [COLOR=green]'Wave Guide[/COLOR]
                    ActiveWorkbook.Names.Add Name:="CoaxConfigList", RefersTo:=ProbeSpec.Range("Empty")
                    lbCoax.Value = 0    [COLOR=green]'Deselect[/COLOR]
                    Instructions.Range("CoaxSizeOutput").Value = "" [COLOR=green]'Clear sheet value[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
            
            [COLOR=green]'Clear GSSG[/COLOR]
            ActiveWorkbook.Names.Add Name:="GSSGList", RefersTo:=ProbeSpec.Range("Empty")
            lbGSSG.Value = 0
            Instructions.Range("GSSGOutput").Value = ""
            
            [COLOR=green]'Deselect Coaxsize[/COLOR]
            lbSize.Value = 0
            Instructions.Range("CoaxSizeOutput").Value = ""
            
            [COLOR=green]'Clear Model[/COLOR]
            ActiveWorkbook.Names.Add Name:="ModelList", RefersTo:=ProbeSpec.Range("Empty")
            lbModel.Value = 0
            Instructions.Range("ModelOutput").Value = ""
            
            
        [COLOR=darkblue]Case[/COLOR] "CoaxConfigListBox"  [COLOR=green]'Coax[/COLOR]
            
            Instructions.Range("CoaxConfigOutput").Value = ProbeSpec.Range(lbCoax.ListFillRange)(lbCoax.Value)  [COLOR=green]'CoaxListBox Value[/COLOR]
            
            [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] lbCoax.Value
                [COLOR=darkblue]Case[/COLOR] 1 [COLOR=green]'Single[/COLOR]
                    ActiveWorkbook.Names.Add Name:="GSSGList", RefersTo:=ProbeSpec.Range("Empty")
                    lbGSSG.Value = 0    [COLOR=green]'Deselect[/COLOR]
                    Instructions.Range("GSSGOutput").Value = ""
                [COLOR=darkblue]Case[/COLOR] 2 [COLOR=green]'Dual[/COLOR]
                    ActiveWorkbook.Names.Add Name:="GSSGList", RefersTo:=ProbeSpec.Range("GSSG")
                    lbGSSG.Value = 1    [COLOR=green]'Default GSSGList 1st item select[/COLOR]
                    Instructions.Range("GSSGOutput").Value = ProbeSpec.Range(lbGSSG.ListFillRange)(lbGSSG.Value)  [COLOR=green]'GSSGListBox Value[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
                    
            [COLOR=green]'Deselect Coaxsize[/COLOR]
            lbSize.Value = 0
            Instructions.Range("CoaxSizeOutput").Value = ""
            
            [COLOR=green]'Clear Model[/COLOR]
            ActiveWorkbook.Names.Add Name:="ModelList", RefersTo:=ProbeSpec.Range("Empty")
            lbModel.Value = 0
            Instructions.Range("ModelOutput").Value = ""
        
        [COLOR=darkblue]Case[/COLOR] "GSSGListBox"
        
            [COLOR=green]'GSSG output[/COLOR]
            Instructions.Range("ModelOutput").Value = ProbeSpec.Range(lbGSSG.ListFillRange)(lbGSSG.Value)  [COLOR=green]'GSSGListBox Value[/COLOR]
        
            [COLOR=green]'Deselect Coaxsize[/COLOR]
            lbCoax.Value = 0
            Instructions.Range("CoaxSizeOutput").Value = ""
        
            [COLOR=green]'Clear Model[/COLOR]
            ActiveWorkbook.Names.Add Name:="ModelList", RefersTo:=ProbeSpec.Range("Empty")
            lbModel.Value = 0
            Instructions.Range("ModelOutput").Value = ""
            
        [COLOR=darkblue]Case[/COLOR] "CoaxSizeListBox"
            
            [COLOR=green]'GSSG output[/COLOR]
            Instructions.Range("CoaxSizeOutput").Value = ProbeSpec.Range(lbSize.ListFillRange)(lbSize.Value)  [COLOR=green]'CoaxSizeListBox Value[/COLOR]
            
            [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] lbSize.Value
                [COLOR=darkblue]Case[/COLOR] 1  [COLOR=green]'0.031[/COLOR]
                    ActiveWorkbook.Names.Add Name:="ModelList", RefersTo:=ProbeSpec.Range("ModelStandard")
                [COLOR=darkblue]Case[/COLOR] 2  [COLOR=green]'0.023[/COLOR]
                    ActiveWorkbook.Names.Add Name:="ModelList", RefersTo:=ProbeSpec.Range("ModelSC")
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
            
            [COLOR=green]'Clear Model[/COLOR]
            lbModel.Value = 0
            Instructions.Range("ModelOutput").Value = ""
            
        [COLOR=darkblue]Case[/COLOR] "ModelListBox"
            [COLOR=green]'Model output[/COLOR]
            Instructions.Range("ModelOutput").Value = ProbeSpec.Range(lbModel.ListFillRange)(lbModel.Value)  [COLOR=green]'ModelListBox Value[/COLOR]
            
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Last edited:

nryan

Board Regular
Joined
Apr 3, 2015
Messages
56
You're the man AlphaFrog. It works! You have helped me tremendously. I apologize if my next question (plea for help) seems greedy as you have already helped me so much.

I have another module with a Sub procedure that tries to match the list box selections with their appropriate pictures and cut dimensions. It is a long If-Then-ElseIf statement (which may not be the best way to do it). Here is a sample of that code:

Code:
Sub Parameters()

    Instructions.Activate
    Master.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


    Dim lbBody  As ListBox: Set lbBody = Instructions.ListBoxes("BodyListBox")
    Dim lbCoax  As ListBox: Set lbCoax = Instructions.ListBoxes("CoaxConfigListBox")
    Dim lbGSSG  As ListBox: Set lbGSSG = Instructions.ListBoxes("GSSGListBox")
    Dim lbSize  As ListBox: Set lbSize = Instructions.ListBoxes("CoaxSizeListBox")
    Dim lbModel As ListBox: Set lbModel = Instructions.ListBoxes("ModelListBox")


    'This hides the warning sign "Hit the 'Refresh' button!" and the red box that surrounds the 'Refresh' button on the "Instructions" worksheet.
    'They are made visible again when different selections are made, reminding the User to refresh the sheet (see ProbeSelectionModule).
    Instructions.Rows("12:12").Hidden = True
    Instructions.Shapes("HitRefresh").Visible = False
    
    'This unprotects the "Master" worksheet allowing the macros to work. It is re-protected at the end.
    Master.Unprotect Password:="wmd"
    


''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''i40''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''


'''Single coax, Angle, 0.03100" coax, i40'''
If lbSize.Value = "0.031" _
And lbCoax.Value = "Single" _
And lbBody.Value = "Angle" _
And lbModel.Value = "i40" _
Then
Instructions.Shapes.Range(Array("Single_Fig1", "Single_Fig2", "Single_Fig3", "Single_Fig4", "Single_Fig5", "Single_Fig6", _
                                "Single_Fig7", "Single_Fig8")).Visible = True
i = 3


'''Single coax, Vertical, 0.03100" coax, i40'''
ElseIf lbSize.Value = "0.031" _
And lbCoax.Value = "Single" _
And lbBody.Value = "Vertical" _
And lbModel.Value = "i40" _
Then
Instructions.Shapes.Range(Array("Single_Fig1", "Single_Fig2", "Single_Fig3", "Single_Fig4", "Single_Fig5", "Single_Fig6", _
                                "Single_Fig7", "Single_Fig8")).Visible = True
i = 4
The problem I am now encountering is Run-time error '13': Type mismatch at the first If statement. I checked my spelling and no errors there but the list box selections are somehow not matching up with my If-Then-ElseIf statement, I think. Any thoughts on how to fix this? Maybe something like this instead?:

Code:
'''Single coax, Angle, 0.03100" coax, i40'''
If lbSize.Value = Case 1 '0.031 _
And lbCoax.Value = Case 1 'Single" _
And lbBody.Value = Case 1 'Angle" _
And lbModel.Value = Case 1 'i40" _
Then
Instructions.Shapes.Range(Array("Single_Fig1", "Single_Fig2", "Single_Fig3", "Single_Fig4", "Single_Fig5", "Single_Fig6", _
                                "Single_Fig7", "Single_Fig8")).Visible = True
i = 3
At the end of the Sub is a For-Next loop is used to copy cut dimensions from cells on the "Master" worksheet to cells on the "ProbeSpec" worksheet. Might not be relevant but here that is:

Code:
Else: Range("error").Value = "ERROR"
End If

'The following will take the cut dimensions corresponding to each probe configuration on the "Master" worksheet (n = row, i = column)
'and populate them in the proper cells on the "ProbeSpec" worksheet.

Dim n As Integer
For n = 12 To 22
    
    Worksheets("ProbeSpecs").Cells(n - 8, 9).Value = Worksheets("Master").Cells(n, i).Value
    
    Next n
 
'This re-protects the "Master" worksheet.
Master.Protect Password:="wmd"


End Sub
Thank you.
 
Last edited:

Forum statistics

Threads
1,081,525
Messages
5,359,272
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top