Run-time error 438 - using With

[Luke]

I'm trying to find out why this code is not working.

With Worksheets("Multi-period Code data").ListBox1
.Clear
For FillCount = 1 To Worksheets("ClassCodes").Range("E1")
.AddItem
Worksheets("ClassCodes").Range("C1").Offset(FillCount, 0)
Next FillCount
.ListIndex = Worksheets("Multi-period Code data").Range("O1").Value
End With

Just to clarify, there is a sheet named "Multi-period Code data" and
ListBox1 exists on the sheet. The error appears to be at the initial "With"
statement. I've tried changing it by moving the ".ListBox1" off the first
line and placing it appropriately within the code, but I keep getting
run-time error 438 as soon as it sees the "With" statement.

How can I make this work?

 

[John Bundy]

I fixed the only error i saw and it worked correctly, but i don't know why it
wasn't showing you the problem in bright red text so maybe something else?
This needs to be together:
.AddItem(Worksheets("ClassCodes").Range("C1").Offset(FillCount, 0))

 

[Luke]

Thanks for the quick reply. I changed the ".AddItem" line as you suggested,
but it is still giving me Run-time error 438, saying "Object doesn't support
this property or method". I've even changed it so that it reads:
With Worksheets("Multi-period Code data")
.ListBox1.Clear
etc.

Then, instead of having the error with the "With" line, it has the problem
with the ".ListBox1.Clear" line. There is a List Box (ListBox1) on the
sheet. It's enabled and it's not locked. I'm at a complete loss.

 

[Ryan H]

You may have inserted a Forms Control. Your code should work fine if
ListBox1 is an ActiveX Control. Hope this helps! If so, let me know, click
"YES" below.

 

[Luke]

I had thought about that. Unfortunately, it is an Active X control.

Is there anything you can think of that I or someone might have done to the
control or to the worksheet (or workbook itself, for that matter) that would
cause this error?

 

[Ryan H]

I'm pretty sure you have name of the ListBox wrong. Do this for me, click
the Design Mode button, right click the ListBox and click Properties, then
look at the (Name) property. Change the (Name) property to ListBox1. Then
run my code. Hope this helps! If so, let me know, click "YES" below.

I also took the liberty to change your code a bit. Also make sure you are
declaring your variables.

Dim FillCount As Long

With Sheets("Multi-period Code data").ListBox1
.Clear
For FillCount = 2 To Sheets("ClassCodes").Range("E1")
.AddItem Sheets("ClassCodes").Cells(FillCount, "C")
Next FillCount
.ListIndex = Sheets("Multi-period Code data").Range("O1").Value
End With

 

[John Bundy]

Check this out, it really HAS to be something with the control, i drug on a
listbox and used your exact code (sheet names and everything) and it worked
fine. Try changing the name of your listbox and drag on a new one, it should
be named listbox1, and run your code. If not, look here
http://www.vbforums.com/showthread.php?t=460591

 

[Luke]

The (Name) property value was already ListBox1. I even checked to see if
maybe there was an extra space at the end, but it was just "ListBox1". And
"FillCount" is dim'd as an integer (it's around 1300 at present and will
never be much more than that).

You're probably going to hate me, but does it make any difference that this
is supposed to run when the workbook is first opened in Excel? I made the
code changes you suggested, but still, when I open the workbook, it hits the
"With Worksheets("Multi-period Code data").ListBox1" and has a cow.

 

[Luke]

I even deleted the original ListBox and put a new one in (appropriately
named) and I still have the same issue whenever the code runs. I didn't
mention it before as I thought it would make no difference, but the fact that
this is supposed to run when the workbook opens shouldn't affect anything,
should it?

 

[Ryan H]

Can you post the rest of the code in your Workbook Open Event? Maybe there
is something else we can see there. And you are absolutely sure it is an
ActiveX control? I have read in some places that ActiveX controls can be
"buggy", but I was able to run the code I posted just fine. Not sure why you
are having issues, so may viewing all the code in the Workbook Open Event
will help.