Form Control List Box - Run-time error 438: Object doesn't support this property or method

[nryan]

Hello!

I'm trying to improve a workbook that uses VBA macros and I have no experience with VBA. I've studdied the current excel workbook and vba code to help me. I've been learning about vba and I've been able to sort out some problems but now I have no idea what's causing my error. Btw, the current workbook macros work fine. I've pretty much copied most of the current macros' vba code. The current workbook uses Form Controls instead of Active X Controls, so that's why I'm also using them (to make things easier on me) even though i'd rather use Active X. Anyway...

Here's the line of vba code I need to debug (Run-time error 438: Object doesn't support this property or method):

Set CoaxConfigListBox = Worksheets("Instructions").ProbeSelect("CoaxConfigListBox")

Here is the vba code from the start of the module up to shortly after the above line of code:

Sub ProbeSelect()

Worksheets("Instructions").Activate
Worksheets("ProbeSpecs").Activate

Worksheets("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")).Visible = False

'Worksheets("Instructions").Rows("9:9").Hidden = False  'shows warning sign (hidden in 'parameters')

''''''''''''''''''''''''''''''''''
'''''''''''Coax Number''''''''''''
''''''''''''''''''''''''''''''''''

Dim CoaxConfigListBox As ListBox
Set CoaxConfigListBox = Worksheets("Instructions").ProbeSelect("CoaxConfigListBox")
Set r = Worksheets("ProbeSpecs").Range(CoaxConfigListBox.ListFillRange)

There is a Form Control textbox called "CoaxConfigListBox" on a worksheet called "Instructions". There is also a worksheet called "ProbeSpecs" in which there is a range called "CoaxConfig" which is the Input Range for the textbox. My code is the same as the current vba code except for the names and this is what confuses me. Any help will be greatly appreciated. Btw, there is a lot more code AND another module called Parameters().

Thank you
-Nick

 

[JoeMo]

I haven't looked at all the code you posted, but try this:

Dim CoaxConfigListBox as Object
Set CoaxConfigListBox = Worksheets("Instructions").Shapes("CoaxConfigListBox")

This assumes you have a forms control list box named "CoaxConfigListBox" on the sheet named "Instructions".

 

[nryan]

Hi JoeMo,

Doing that fixes the Run-time error on that line, but then I get the same Run-time error (Run-time error 438: Object doesn't support this property or method) on the next line:

Set r = Worksheets("ProbeSpecs").Range(CoaxConfigListBox.ListFillRange)

This is the code form the original which I copied. Maybe it will help. Btw, the code below works.

Sub DropDowns()

Instructions.Shapes.Range(Array("Zero_Dual", "Zero_Single", "RCS", "RCD", "Z0_Dual", "Z0_Single", "RSD", "TCMS", "TCSS", "TCSD", "TCMD", "FSMS", "FSSS", "FSMD", "FSSD" _,
                                "GSSGM", "GSSGS", "FTMS", "FTSS", "FTGDM", "FTMD", "FTDGS", "FTSD", "BSM", "BSS", "BDGM", "BMD", "BDGS", "BSD", "TextBoxMini")).Visible = False

Instructions.Rows("9:9").Hidden = False  'shows warning sign (hidden in 'parameters')

'''''''''''Coax Number'''''''''''

Dim CN As DropDown
Set CN = Instructions.DropDowns("ddCxN")
Set r = Master.Range(CN.ListFillRange)

The only differences are the names I think. My module is called "ProbeSpecs()" instead of "DropDowns()". I've renamed the pictures ("Zero_Dual", "Zero_Single", "RCS", etc.). The original uses a Form Control Combobox and I use a Form Control Listbox. I use the variable "CoaxConfigListBox" instead of "CN" and again "CoaxConfigListBox" instead of "ddCxN". The original has a combobox on the worksheet "Instructions" called "ddCxN".

Also, the original code is somehow able to identity worksheets without using worksheets("Name of Worksheet")​ which you can see in the code. The original has worksheets named "Instructions" and "Master". Mine has worksheets "Instructions" and "ProbeSpecs".

 

[JoeMo]

Try replacing this:

Set r = Worksheets("ProbeSpecs").Range(CoaxConfigListBox.ListFillRange)

with this:
Dim r as range
Set r = Worksheets("ProbeSpecs").Range(CoaxConfigListBox.ControlFormat.ListFillRange)

 

[nryan]

That stopped the error, but the macro wasn't behaving as the original and in the second module I had a "mismatch error" in an If-Then-ElseIf statement. You've been helpful though. I decided to make a copy of the original and change it to what I want rather than continue with my workbook that I started from scratch. And it's working, I've gotten closer to completing this mess than ever. There must be some things in the original workbook, not the vba code that I didn't realize.

Thanks Joemo.