HELP ON THIS ERROR : "RUN TIME ERROR 438: OBJECT DOESN'T SUPPORT THIS PROPERTY OR METHOD"

[chazrab]

I keep getting this error on opening the Excel app. Sometimes the error generates and sometimes it doesn't.
The difficult part for me is figuring out why Excel doesn't like this line of code. As I see it, the code is not violating
any rules. Images with code below. The only other thing I can say is that this Listbox1 is on a Sheet, not a userform.
But that shouldn't make any difference, should it ?

Private Sub ListBox1_Click()
Dim n As Long
n = ListBox1.ListIndex
Sheets("BIBLETEXT").TextBox3.Value = ListBox1.List(n, 1) _  ----->'yellow highlight error starts at this inis line 
& vbCrLf _
& vbCrLf _
& ListBox1.List(n + 1, 1) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 2, 1) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 3, 1) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 4, 1) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 5, 1) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 6, 1) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 7, 1) _
& vbCrLf

With the code above and images below, can anyone "see" what's causing the error in the code excel doesn't like.
Thanks for anyone's help. cr

 

[RoryA]

Perhaps the textbox on BIBLETEXT is not called TextBox3 or is not an activex textbox.

 

[chazrab]

Hi Rory, thx for trying to help. Image below shows large textbox is Textbox3 as the Name Dropdown in upper left confirms, and the Sheet Tab
in the lower left is "popped out" as the Activesheet.

Listbox1 highlighted in blue above Textbox3 is the Listbox whose Click event generates this error on opening the
workbook.

The quirky thing is that when a change is made and saved and the workbook is closed and reopened, the error
is generated. When no changes are made in the app, and it is closed and reopened, the error isn't generated. As I
mentioned, I don't see a thing wrong with this code line. The error generation and not regeneration is making finding
the root of this problem very difficult

Sheets("BIBLETEXT").TextBox3.Value = ListBox1.List(n, 1) _   ----->yellow highlight begins at this line.

Should this not be the correct way to refer to the Textbox3 text object on the activesheet ?

Thanks for all your help.

cr

 

[RoryA]

My guess is that you're running into an age-old activex issue with different versions of the forms library. I'd suggest you replace the activex controls with form controls if you can.

 

[chazrab]

Hi Rory - I can do that and will do that if I must, if I can get this app to work the way it should. Right now, I'm moving this whole set of objects
that comprise this app, consisting primarily of textboxes, listboxes and buttons along with all of the underlying code to one userform which would
necessitate using form controls as you mentioned above.

If, when all of these objects are ported over to a userform, and it runs flawlessly after consistent and rigorous testing, I'm guessing that Excel much prefers
compiling and running VBA apps with its objects placed on a self-contained userform and have the underlying data reside only in sheet cells, rather
than using ActiveX controls on a sheet to create a VBA app to try to accomplish the same thing.

Can't thank you enough for the support and help you've given myself and others through the years.

cr

 

[RoryA]

I'm moving this whole set of objects
that comprise this app, consisting primarily of textboxes, listboxes and buttons along with all of the underlying code to one userform which would
necessitate using form controls as you mentioned above

Actually, in spite of the name, form controls are not the ones you use on userforms (those are actually activex, but as long as they are not on a worksheet, should not cause the same problem). Form controls are the older controls that are part of the excel object model - they have less functionality but are generally more stable.

Thank you for the kind words!

 

[chazrab]

Hi Rory
The entire app of forms, buttons and listboxes with all underlying code was moved to one large userform which occupies the entire screen. Tested and retested and no bugs or error messages. The takeaway on this, as we discussed, is that Excel's compile engine prefers objects put on userforms rather than as ActiveX controls on
a sheet, and have the sheets reserved for data only - at least that's what I'm finding here.

I downloaded John Walkenbach's KJV file from the web, which he developed some years ago. That file alone comprises 31,103 rows of data, one verse per row from Gen to Rev. The FIND method's code seems to run just as fast down that many rows as a 10 row file. For parallel comparisons of modern versions, I also had to copy and paste the same row content from the NIV, NASB and RSV, which was available from the web. Point being, FIND performs well over a range from A1:D31103
when doing a verse, word or phrase search between quotes. Lot of work, but also a lot of fun. VBA is a great tool for experimenting with creativity.

Thanks again for all your help.

cr
Kingwood, Tx