+ Reply to Thread
Results 1 to 7 of 7

Compile error referencing checkbox in another sheet

  1. #1
    Registered User
    Join Date
    04-07-2016
    Location
    Bury St Edmunds, England
    MS-Off Ver
    2010
    Posts
    8

    Compile error referencing checkbox in another sheet

    Hi everyone (Ok, it's my first post...)

    I have some code behind one sheet (sheet1) which includes a routine to hide/unhide some rows in another sheet (sheet2) in the same workbook and enable/disable some CheckBoxes. The original version (which worked) included a sheet1.select statement and then used statements such as sheets("sheet2").rows(x:y).entirerow.hidden = True/False to hide the x:y rows. Sheet2 also has a couple of checkboxes eg checkbox1 which it enabled/disabled using sheets("sheet2").checkbox1.enabled = True.

    In attempting to tidy this up (more fool me) I have sought to avoid the 'select' statement (as I don't need to change any values in sheet2, just hide/enable as above) and have also set a worksheet variable in place of sheets("sheet2")

    So I start with something like..

    Please Login or Register  to view this content.
    I then have statements like

    Please Login or Register  to view this content.
    When I try and run this I get a compile error - 'Method of Data member not found' referring to the ws.checkbox1.enabled = true line. I did think this might be connected to the .exd file issue but having deleted these it makes no difference.

    if I comment out this offending line and replace it with sheets("sheet2").checkbox1.enabled = true, then all is well.

    Am I missing something? Could it be that whilst hiding lines in another sheet can be done without a select statement (ie without making Sheet2 the active sheet) this does not apply to checkboxes? I've tried adding a ws.select or even a sheets("sheet2").select statement but I still get the same compile error.

    Any ideas...?

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,447

    Re: Compile error referencing checkbox in another sheet

    Selecting the sheet object in order to perform some actions on it is not required.

    ws.checkbox1.enabled = true will not recognize the checkbox1 on sheet2 so rather qualify the checkbox1 with the sheet code name
    i.e.
    Sheet2.CheckBox1.Enabled=True

    Where Sheet2 is the code name of Sheet2 so if you rename the Sheet2 and give it another name, the code name still remains intact i.e. Sheet2.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,221

    Re: Compile error referencing checkbox in another sheet

    I am not sure exactly what the issue is but I suppose it is related to dynamic resolution of object attributes in VBA. I was able to reproduce this. I have seen the same kind of issue when trying to call a Sub in a worksheet where the worksheet is an object variable.
    Please Login or Register  to view this content.
    Rather than deferring the resolution of the reference to runtime, it attempts to resolve it at compile time. At compile time it says, "Hey, a worksheet does not have an attribute called CheckBox1, so this can't compile."

    But that does not explain why the third example works. I suspect it might be that VBA says, "Well, I don't really know the objects contained by Worksheets("Sheet1") at compile time, so I'm just going to ignore this and let it be handled at runtime."

    Given that, I cannot understand why the second example does not follow the same logic, and defer resolution to runtime.

    In short, to understand why this is a problem you would have to get more insight as to the design rationale for VBA. But you have found a valid issue and you are just going to have to code around it, as you have done or as sktneer suggests.
    Last edited by 6StringJazzer; 04-07-2016 at 10:34 AM.
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  4. #4
    Registered User
    Join Date
    04-07-2016
    Location
    Bury St Edmunds, England
    MS-Off Ver
    2010
    Posts
    8

    Re: Compile error referencing checkbox in another sheet

    Sktneer & Jazzer, many thanks for your feedback. Jazzer, your examples show exactly the problem.

    The way the code was first written used the full (option3) reference and this worked. Trying to be (too) smart I started with the dim/set ws and then tried to use a With/End With block for ws. This failed, but could well have been for the same reason. I then took out the With and prefixed the references with ws. rather than the full sheets("sheet1"). This is when I identified the specific issue.

    Logically I don't get why a ws.rows() reference compiles ok when the ws.checkbox does not,but as you say it may be just the way it is with VBA and in this instance I need to use the full reference, if only for the checkbox.

    At least I'm happy that I'm not going mad (yet).

    Thanks again guys

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,221

    Re: Compile error referencing checkbox in another sheet

    Quote Originally Posted by BacktoCode View Post
    At least I'm happy that I'm not going mad (yet).
    We are all going mad together. Welcome!

  6. #6
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Compile error referencing checkbox in another sheet

    If you declare variable as worksheet, you tell compiler that variable will implement Worksheet interface and no more. This interface does not have CheckBox1 property because not all worksheets will have this control. Compiler must complain about this. Sheet1 interface is for that sheet only and does have this property. You must use this or generic Object (which will only resolve at runtime with IQueryInterface call)
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  7. #7
    Registered User
    Join Date
    04-07-2016
    Location
    Bury St Edmunds, England
    MS-Off Ver
    2010
    Posts
    8

    Re: Compile error referencing checkbox in another sheet

    Hi Izandol

    Yes, that makes sense to me and would explain why the .rows() worked (which every sheet has) wheras the .checkbox1 did not, because as you say this is not generic to a sheet and has to be added.

    Must look up IQueryInterface call, as this sounds a little Star Wars for me at the moment. Thanks again, for bringing the light to my tunnel!

    Rgds

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compile error hidden in sheet 1
    By sam94 in forum Excel General
    Replies: 6
    Last Post: 03-06-2015, 10:04 AM
  2. Compile error in hidden module: Sheet 1
    By rookrose in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-28-2014, 01:59 AM
  3. Compile error when trying to Copy from one sheet to another sheet
    By cartica in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2014, 03:05 PM
  4. Compile error in hidden module sheet 1
    By Paramesh M in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-06-2013, 08:32 AM
  5. [SOLVED] Filter by ref cell Value and copy to new sheet. Compile Error: Sub or Function not Defined
    By moxman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2013, 11:56 PM
  6. [SOLVED] Compile Error in Hidden Module and Compile Error: Can't find project or library
    By Taislin in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-10-2013, 07:03 PM
  7. Compile Error in Hidden Sheet
    By Simon Austin in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-15-2009, 11:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1