none
Run-time error '1004' RRS feed

  • Question

  • I am getting a strange error that I do not understand. Here is my VBA code:

    Sub Main()
        Dim wbThis As Workbook
        Set wbThis = Workbooks("MyBook.xlsb")
        Dim bFoundSheet As Boolean
        bFoundSheet = False
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.Calculation = xlCalculationManual
        For Each ws In wbThis.Worksheets
            If ws.Name = Format(Date, "mm-dd-yy") Then
                bFoundSheet = True
            End If
        Next
        
        If Not bFoundSheet Then
            wbThis.Worksheets("AttendanceTemplate").Visible = xlVisible
            wbThis.Worksheets("AttendanceTemplate").Activate
            wbThis.Worksheets("AttendanceTemplate").Select
            wbThis.Worksheets("AttendanceTemplate").Copy After:=Sheets("AttendanceTemplate")
            wbThis.Worksheets("AttendanceTemplate (2)").Select
            wbThis.Worksheets("AttendanceTemplate (2)").Name = Format(Date, "mm-dd-yy")
            wbThis.Worksheets("AttendanceTemplate").Visible = xlHidden
        End If
    End Sub

    The worksheet AttendanceTemplate starts off as hidden. When it gets to the line:

    wbThis.Worksheets("AttendanceTemplate").Visible = xlVisible

    It gives me the error:

    Run-time error '1004': Unable to set the Visible property of the Worksheet class.

    So, I try commenting that line out and running it again. Now, it gets to this line:

    wbThis.Worksheets("AttendanceTemplate").Select

    And gives me this error:

    Run-time error '1004': Select method of Worksheet class failed.

    Then, I make the worksheet AttendanceTemplate visible (still keeping the one line commented out), and run the script, and it runs perfectly (at the end, the worksheet is hidden again). But, now, if I were to run the script tomorrow, I would get the same error again. I don't want to have to manually make the sheet visible every day. How do I get around this?

    Wednesday, November 16, 2016 2:40 PM

Answers

  • Re:  value of constants

    xlSheetVisible = -1
    True = -1
    xlVisible = 12

    There are about 2000 XLConstants and between 250 to 500 vbConstants (there are two overlapping lists)
    The guys who created Excel had to be a little weird or were very rushed...  xlZero has a value of 2

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)


    Thursday, November 17, 2016 5:40 AM
  • Hi,

    >>Run-time error '1004': Unable to set the Visible property of the Worksheet class.

    Please visit Worksheet.Visible Property (Excel)

    To unhide sheet, you could use True or xlSheetVisible according to XlSheetVisibility Enumeration (Excel).

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 18, 2016 2:27 AM
    Moderator

All replies

  • Weird. I changed the line:

    wbThis.Worksheets("AttendanceTemplate").Visible = xlVisible

    To:

    wbThis.Worksheets("AttendanceTemplate").Visible = True

    Now it works great. That is very odd behavior. Does anyone know if this is the correct workaround? It worked today, but I am not sure if this is a permanent solution. Why did it not accept xlVisible?

    Wednesday, November 16, 2016 2:46 PM
  • Re:  value of constants

    xlSheetVisible = -1
    True = -1
    xlVisible = 12

    There are about 2000 XLConstants and between 250 to 500 vbConstants (there are two overlapping lists)
    The guys who created Excel had to be a little weird or were very rushed...  xlZero has a value of 2

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)


    Thursday, November 17, 2016 5:40 AM
  • Hi,

    >>Run-time error '1004': Unable to set the Visible property of the Worksheet class.

    Please visit Worksheet.Visible Property (Excel)

    To unhide sheet, you could use True or xlSheetVisible according to XlSheetVisibility Enumeration (Excel).

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 18, 2016 2:27 AM
    Moderator