How to Retrieve hidden sheets in an Excel add-in?

user_7620393 0
Viewed 255

I have built an Excel add-in using VBA. I have used 2 sheets containing some data while creating this add-in. When i saved it as an add-in, both sheets became hidden while add-in still works. Now i want to make few changes in the data of those sheets and code as well. How can i see those sheets hidden in add-in? Here is an screenshot attached. Here it is clear that there are two sheets ( Sheet1,Sheet9) are present but not visible. how can i retrieve these sheets?

screenshot

vba excel
2 Answers

Select ThisWorkbook. In the Properties pane, set the IsAddIn value to False. This will make the add-in's workbook (including all its sheets) visible and you can make changes to the data.

Caveat: You won't be able to save the workbook normally; doing so will give you an error and then a prompt to save it with a new name.

What you need to do instead is this: Once you have made the changes to your data, set the IsAddIn property of the workbook back to True. That will make the sheets hidden again. You can then save your add-in project from within the VBA editor.

user_35051880

The file would not let the user save a xla/xlam file if ".IsAddin" is false (worksheets add-in must be hidden to be saved) If you want to use the add-in but let the user see the hidden sheets by code instead of manually you can use the following:

Sub buttonclick()
If ThisWorkbook.IsAddin Then
 ThisWorkbook.IsAddin = False
End If
End Sub

' Put code in Thisworkbook 
    Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not ThisWorkbook.IsAddin Then
     ThisWorkbook.IsAddin = True
    End If
    End Sub
user_107216750
Related