+ Reply to Thread
Results 1 to 13 of 13

Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

  1. #1
    Registered User
    Join Date
    04-23-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    41

    Exclamation Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    Hi,

    I am in the final stage of completing a file project but I am receiving the following error:

    Runtime error 1004: Method 'Visible' of Object '_Worksheet' failed.

    The last two tasks are 1) to prevent hidden sheet and column access and 2) currently closing the file from the "X" or File > Exit as both create the error message. If this can be solved it can be fine to close from the X or File > Exit as i will then add File Saving methods.

    I understand the visiblity status of the worksheet is causing the error but how to correct or capture the error without leaving the workbook and/or worksheets unprotected and vulnerable to editing.

    I have included a cut-down generic version of the worksheet.

    The premise is staff update the fields in yellow and at the begining of each week the individual worksheets are reset i.e. the values of the Updated column become "No".

    There is a Main Menu tab with Service buttons which change from red to green when all client details have been updated for the week in question.

    There is an Admin tab currently used to store the "Reset Worksheet" button method but this tab can be removed if there is a more elegant solution.

    If the solution I seek is not possible, what is a more logical way of preventing tampering within parts of the workbook.

    Thanks
    Darren
    Attached Files Attached Files

  2. #2
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    LOL..
    How would I get the error....in this...please clarify.
    Lokesh Kumar
    Stay Hungry.. Stay Foolish..
    _________________________________________________________
    Please Click STAR to Add Reputation if my/someone's answer helped!

  3. #3
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    You have a lot of code but haven't told us in what code and on what line the error occurs. I'm using x to close and file exit and get no errors.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  4. #4
    Registered User
    Join Date
    04-23-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    Sorry my bad,

    If you open one fo the services from the main menu you are taken to that worksheet and the main menu becomes hidden. Clicking on X to close will cause the run time error.

  5. #5
    Registered User
    Join Date
    04-23-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    The error occurs on the following line (highlighted in red):

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    If you open one fo the services from the main menu you are taken to that worksheet and the main menu becomes hidden. Clicking on X to close will cause the run time error.
    Its not giving me any error..

  7. #7
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    Quote Originally Posted by LokeshKumar View Post
    Its not giving me any error..
    I'm not getting an error either.

  8. #8
    Registered User
    Join Date
    04-23-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    Are the worksheets protected at your end? the password is blank i.e. select OK

  9. #9
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    Quote Originally Posted by Sorjas View Post
    Are the worksheets protected at your end? the password is blank i.e. select OK
    Yeah it was protected, I tried it with both protected and unprotected. I put a break point in the code and it ran over that visible line without a glitch.

  10. #10
    Registered User
    Join Date
    04-23-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    I remember there is a setting somewhere do with error reporting could I have something switched on that you have switched off or vice-versa?

  11. #11
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    With the On Error Resume Next just before that line, I'll be more surprised if there are any errors popping up.

    Anyhoo, it ran fine here.

    EDIT:
    I think you had the issue before you put in the On Error Resume Next. In which case, the reason why you there is an error is because you cannot hide ALL the sheets without having at least 1 visible. You'll need to amend your code to unhide MAIN_MENU first...

    Please Login or Register  to view this content.
    Last edited by quekbc; 07-15-2015 at 02:21 AM.

  12. #12
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    With the On Error Resume Next just before that line, I'll be more surprised if there are any errors popping up.
    Duh. Man I didn't even notice that. Time for bed.
    Good Luck.

  13. #13
    Registered User
    Join Date
    04-23-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Runtime Error: 1004 Method 'Visible' of Object "_Worksheet' failed

    Thank you quekbc and skywriter.

    Just to be sure I turned off "break on all errors" and the issue went way. So I will make the mods suggested and test it.


    EDIT: Thanks quekbc that worked.
    Last edited by Sorjas; 07-15-2015 at 02:33 AM.

+ 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. Run-Time Error '1004': Method 'Visible' of Object'_Worksheet' Failed
    By caltman242 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-14-2015, 10:43 AM
  2. [SOLVED] Runtime Error 1004; Method 'OLEObjects' of object '_Worksheet' failed
    By Adnamut in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2014, 01:21 AM
  3. Runtime Error '1004': Method 'Pivot Tables' of object '_Worksheet' Failed
    By ZuneidDassu in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-07-2014, 06:21 AM
  4. [SOLVED] Runtime Error '1004' Method 'Range' of object '_Worksheet' failed
    By ovalstar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2013, 02:15 PM
  5. [SOLVED] Help! Error 1004, method 'Range' of object'_Worksheet' failed.
    By zac_595 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-07-2013, 02:55 PM
  6. [SOLVED] Runtime error 1004: Method 'Range' of object '_Worksheet' failed
    By Phil Payne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2013, 01:30 AM
  7. Receiving runtime error 1004: Method 'Range' of object '_Worksheet' failed
    By DrShocktopus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2010, 10:39 AM

Tags for this Thread

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