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

nryan

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

HTML:
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:

HTML:
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
I haven't looked at all the code you posted, but try this:

Code:
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

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

HTML:
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.

HTML:
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".
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
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

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

Forum statistics

Threads
1,082,019
Messages
5,362,711
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top