COMPILE ERROR WHEN SHEET OPENS FROM DESKTOP...

[chazrab]

When the file opens from the desktop, a compile error message is generated saying that the' method or data method not found.' pointing to Textbox1 in blue
and the Listbox1 line highlghted in yellow. See image below. However, both Listbox1 and Textbox1 are present on the sheet. They are jut hidden until
the View data button is clicked

Me.Listbox1.visible = false
Me.Textbox1.visible = false

Yet they both become visible and work perfectly when the View data button is clicked with this code when the sheet is active:

Private Sub cmdVISIBLE_Click()
Me.ListBox1.Visible = True 
Me.TextBox1.Visible = True 
Me.TextBox1 = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
End Sub

How can this be? Why is Excel saying that the 'method or data member not found' when it is definitely present and clearly visible on the sheet ? I tried making both
visible to see if the visible property was generating the error, and the same result: 'method or data member not found --->pointing to Textbox1.
There is Textbox1 in the image on the right of Listbox1 below.

Googling tells to find and delete a file called MSForms.something to get it to work. Why should anyone have to delete any Excel files to
get Excel to work properly? I've never heard of this before!

Please help if you can. Thanks, cr

 

[Siddharth Rout]

There is nothing wrong with the Error message. It works as expected.

You have attached the word Me to it, which implies that the workbook is the parent of the controls. It is not. It is the sheet where the control is placed. Change it to the relevant sheet and it will work. For example:

Option Explicit

Private Sub Workbook_Open()
    Sheet1.ListBox1.Visible = False
    Sheet1.TextBox1.Visible = False
End Sub

 

[chazrab]

Hi Siddharth - thanks for the reply. Wish your solution were that easy a fix. A new error is generated when I referred directly to the Sheet name. In this case, I just
renamed the sheet to "OPEN". But renaming a sheet shouldn't make any difference, should it? Below are images of the code and new error. Is there a quirk in Excel that's causing this?
Otherwise, what can I be doing wrong? The only other thing I can add is this app has a sheet with 3 columns that have 31103 rows of data. But that shouldn't make
any difference, should it? Thanks again for your trying to help me.
cr

 

[Siddharth Rout]

Hi Siddharth - thanks for the reply. Wish your solution were that easy a fix. A new error is generated when I referred directly to the Sheet name. In this case, I just
renamed the sheet to "OPEN". But renaming a sheet shouldn't make any difference, should it? Below are images of the code and new error. Is there a quirk in Excel that's causing this?
Otherwise, what can I be doing wrong? The only other thing I can add is this app has a sheet with 3 columns that have 31103 rows of data. But that shouldn't make
any difference, should it? Thanks again for your trying to help me.
cr

This is a different error caused by a different reason. My solution was to solve the error which you were getting in Workbook_Open()

Do you still get that error?

Regarding the new error, if the Listbox is in the relevant Sheet Code area, then you do not need to qualify it with a sheet. You can directly use ListBox1.List(ListBox1.ListIndex, 1). Same with TextBox. BTW how are you populating ListBox1? May I see that code?

 

[chazrab]

Removed sheet references on both objects. Here we go again. Have a look at what images below show. Also included the way
Listbox1 is populated through the standard Properties window (ListFillRange) in yellow. Something seems to be confusing Excel.
Putting ActiveX Listbox and Textbox controls on a sheet and this code should be very strightforward and simple. However, someting's working
here that keeps bugging this code out. The file is included. Maybe you can figure this out. Thanks once again for all your help.
cr

Loading Google Sheets