none
Unhide VBA for excel sheet fails. RRS feed

  • Question

  • I am using the following macro to unhide a worksheet:

    Sub UnhideAllSheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    ws.Visible = xlSheetVisible
    Next ws
    End Sub

    It fails with error Object Variable or with block Variable not set.

    How can this be resolved?

    Thursday, January 9, 2014 5:44 AM

All replies

  • Hi Basma,

    Do you have any workbooks opened in Excel?

    For example, you can use the following code for checking whether the object is set:

    If obj Is Nothing Then
        ' need to initialize obj: '
        Set obj = ...
    Else
        ' obj already set / initialized. '
    End If

    or the opposite way:

    If Not obj Is Nothing Then
        ' obj already set / initialized. '
    Else
        ' need to initialize obj: '
        Set obj = ...
    End If
    Also please make sure that your document is not opened in the Protected View mode.
    Thursday, January 9, 2014 11:56 AM
  • Hi Basma

    If this is related to your other problem, saving the embedded object to a file, then the problem comes from the workbook being hidden. If the workbook is hidden then you can't access the worksheets, I believe. See my answer to the other thread

    http://social.msdn.microsoft.com/Forums/office/en-US/9e958918-408b-493b-9f75-1c6f03bd2180/embedded-excel-sheet-in-powerpoint-when-saved-using-saveas-call-gets-saved-in-hidden-state?forum=officegeneral


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, January 9, 2014 5:16 PM
    Moderator
  • Hi, 

    After running ther code, I find it works well if your workbook is normal. 

    So I suggest you checking whether the worksheets showing in the VBE is correspond to the displaying worksheets and the hidden worksheets as followed. If not, it means some worksheet have been lost or added as an exception. You may need to create a new workbook and copy the data from the current workbook to it.

     

    Since we cannot repro your issue, would you mind sharing your sample Workbook with us by SkyDrive?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, January 16, 2014 12:14 PM
    Moderator