CODEWITHSUNDEEP
vbaexcel
Ashish Baboo
Ashish Baboo

Reputation: 154

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

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

Upvotes: 3

Views: 545

Answers (2)

Noam Brand
Noam Brand

Reputation: 346

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 Showbutton()
If ThisWorkbook.IsAddin Then
 ThisWorkbook.IsAddin = False
End If
End Sub

Sub Hidebutton()
If not ThisWorkbook.IsAddin Then
 ThisWorkbook.IsAddin = True
End If
End Sub

An Excel Error Measage will be shown but it will work after the user presses 'oK'

UNSOLVED-Suppress an Excel Error message caused by save button before going into Workbook_BeforeSave event

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
            If Not ThisWorkbook.IsAddin Then
             ThisWorkbook.IsAddin = True
            End If
End Sub

Upvotes: 0

Patrick Wynne
Patrick Wynne

Reputation: 2124

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.

Upvotes: 6

Related Questions