COMPILE ERROR WHEN SHEET OPENS FROM DESKTOP...

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
877
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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:
Code:
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
 

Attachments

  • OPEN EVENT.jpg
    OPEN EVENT.jpg
    20.3 KB · Views: 12
  • COMPILE ERROR WHEN FILE OPENED FROM DESKTOP.jpg
    COMPILE ERROR WHEN FILE OPENED FROM DESKTOP.jpg
    69.3 KB · Views: 14
  • WORKS WHEN VIEW DATA BUTTON CLICKED. NO COMPILE ERRROR MESSAGE.jpg
    WORKS WHEN VIEW DATA BUTTON CLICKED. NO COMPILE ERRROR MESSAGE.jpg
    88.4 KB · Views: 12
Last edited:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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:

VBA Code:
Option Explicit

Private Sub Workbook_Open()
    Sheet1.ListBox1.Visible = False
    Sheet1.TextBox1.Visible = False
End Sub
 
Upvote 0
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
 

Attachments

  • NEW ERROR WHEN REFERENCING THE SHEET AS THE PARENT.jpg
    NEW ERROR WHEN REFERENCING THE SHEET AS THE PARENT.jpg
    64.5 KB · Views: 10
  • AND HERES THE ERROR LINE IN YELLOW.jpg
    AND HERES THE ERROR LINE IN YELLOW.jpg
    78.4 KB · Views: 12
Upvote 0
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?
 
Upvote 0
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

 

Attachments

  • VARIABLE NOT DEFINED ERROR THIS TIME WHEN EXCEL IS JUST TRYING TO OPEN THE LISTBOX CLICK EVENT.jpg
    VARIABLE NOT DEFINED ERROR THIS TIME WHEN EXCEL IS JUST TRYING TO OPEN THE LISTBOX CLICK EVENT.jpg
    24.4 KB · Views: 6
  • POPULATING THE LISTBOX THE STANDARD WAY  IN PROPERTIES LISTFILLRANGE.jpg
    POPULATING THE LISTBOX THE STANDARD WAY IN PROPERTIES LISTFILLRANGE.jpg
    83.9 KB · Views: 7
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top