none
Run Time Error 1004 Method Worksheet of object '_Global Failed

    问题

  • Please help.

    I found this error in below codes.

    'Created by Marlton A. Soliven
    Option Explicit
    Option Compare Text
    'Password to unhide sheets
    Const pWord = "*****"

    Sub HideSheets()
    'Set worksheet to Very Hidden so that it can only be unhidden by a macro
        Worksheets("Accounting").Visible = xlSheetVisible     (Error found here!)

    End Sub

    Sub ShowSheets()
    'Prompt the user for a password and unhide the worksheet if correct
        Select Case InputBox("Please enter the password to unhide the sheet", _
            "Enter Password")
           
            Case Is = pWord
                With Worksheets("Accounting")
                    .Visible = xlSheetVisible
                    .Activate
                    .Range("A1").Select
                End With
            Case Else
                MsgBox "Sorry, that password is incorrect!", _
                    vbCritical + vbOKOnly, "You are not authorized!"
        End Select
    End Sub

    Option Explicit

    Private Sub Workbook_Open()
    'Turn off screen updates
        Application.ScreenUpdating = False

    'Hide confidential sheet at startup
        Call HideSheets
       
    'Activate cell A1 on the Dashboard sheet at startup
        With Worksheets("Dashboard")
            .Activate
            .Range("A1").Select
        End With

    'Restore screen updates
        Application.ScreenUpdating = True
    End Sub

     

    Appreciate anyone who can assist me.

    Thank so much.

    • 已移动 Cindy Meister MVPMVP 2012年7月11日 14:23 Excel-specific question (From:General Office Development)
    2012年7月9日 0:59

答案

  • Is your workbook protected ?

    Is your Accounting sheet is only one sheet ?

    And as suggested by Zhang use xlSheetVeryHidden to hide from user interafce manipulation.

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and Mark as Answer if it solves the issue.

    2012年7月11日 15:37
  • Hi Marlton_07,

    Thank you for posting in the MSDN Forum.

    I've tried your code, however I cannot reproduce your scenario. I'm using Office 2010. Which version of Office you are working with?

    Besides, by using Sub HideSheets you want to hide the sheet Accounting, however in your code you've written 

    Worksheets("Accounting").Visible = xlSheetVisible

    which can not realize your purpose. I think it's proper to set the visible property to xlSheetVeryHidden.

    Hope it helps.

    Best regards,
    Quist



    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us

    2012年7月11日 6:47

全部回复

  • Hi Marlton_07,

    Thank you for posting in the MSDN Forum.

    I've tried your code, however I cannot reproduce your scenario. I'm using Office 2010. Which version of Office you are working with?

    Besides, by using Sub HideSheets you want to hide the sheet Accounting, however in your code you've written 

    Worksheets("Accounting").Visible = xlSheetVisible

    which can not realize your purpose. I think it's proper to set the visible property to xlSheetVeryHidden.

    Hope it helps.

    Best regards,
    Quist



    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us

    2012年7月11日 6:47
  • Is your workbook protected ?

    Is your Accounting sheet is only one sheet ?

    And as suggested by Zhang use xlSheetVeryHidden to hide from user interafce manipulation.

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and Mark as Answer if it solves the issue.

    2012年7月11日 15:37