Hide sheets with VBA code

VBAAPI
10 posts / 0 new
Log in or register to post comments.
Last post
8 years ago
#1
X 0
Hide sheets with VBA code

Hi guys

Is it possible to hide sheets with VBA code in modular workbook?

I tried but it throws some error and the code is working with other files.Could you clarify?

See code below:

Sub SwitchToUserMode()
    Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Sheets
        If wks Is ActiveSheet Or InStr(1, wks.Name, "User") > 0 Then
            wks.Visible = xlSheetVisible
        Else
            wks.Visible = xlSheetHidden
        End If
    Next wks
End Sub

 

8 years ago
Michael Hutchens A+ 162

Hi Yury,

You will need to use the Modano API to do things like hide/unhide sheets within modular workbooks. Download it here: www.modano.com/api/latest

We regularly use this API when providing model build support to clients, as it enables all sorts of things to be automated way above and beyond what you can normally do with VBA.

I've extracted a very small part of the Modano API (the class named ModanoApplication) and put it in the attached workbook along with the following code to hide all sheets other than the cover sheet:

Sub Hide_Non_Cover_Sheets()
    
    'Hides all sheets other than the cover sheet.
    
    'Private declarations:
    Dim fContinue As Boolean
    Dim strErrorMessage As String
    Dim modappConnection As New ModanoApplication
    Dim wsCur As Excel.Worksheet
    
    'Checks for valid connection to Modano:
    fContinue = modappConnection.ValidConnection(Nothing, False)
    
    'Gets the projects collection:
    If fContinue Then
        For Each wsCur In ThisWorkbook.Worksheets
            If (wsCur.Name <> "Cover") Then
                If (InStr(wsCur.Name, "ModanoMeta") = 0) Then
                    If (Not modappConnection.SetSheetVisibility(wsCur, xlSheetHidden)) Then
                        fContinue = False
                        strErrorMessage = "The sheet named '" & wsCur.Name & "' could not be hidden."
                        Exit For
                    End If
                End If
            End If
        Next wsCur
    End If
    
    'Warns if error:
    If fContinue Then
        Call MsgBox("All non-cover sheets are hidden.", vbInformation, "Modano API")
    Else
        Call MsgBox(strErrorMessage, vbInformation, "Modano API")
    End If

End Sub

Note that you will need to explicity ignore the worksheet named 'ModanoMeta', as this very hidden worksheet contains the modular data required for Modano to work, so it can't be unhidden using the API.

Attachments: 
Modano API-Show Hide Sheets VBA Example.xlsb
8 years ago
Michael Hutchens A+ 162

Apologies Yury - I should have given you more precise instructions rather than just casually mentioned the ModanoApplication class. This class is the class that connects your VBA to the Modano add-in.

There's actually a full object library of classes in the Modano API on top of this. I'll let you know when we release it.

7 years ago
Michael Hutchens A+ 162

Hi Tarjei, sorry for the belated response.

The easiest way to use the Modano API is to simply include all of its classes when importing the API code. I can walk you through how to do this via a GoToMeeting if you like.

M.

6 years ago
Michael Hutchens A+ 162

Hi James,

We haven't publicly released the Modano API as such, but we're happy to provide it to users on request.

Please email api@modano.com as ask for the latest version if you'd like to try it.

M.

5 years ago
Michael Hutchens A+ 162

Hi Guys,

We now allow users to download the latest version of the Modano API using this link: www.modano.com/api/latest

M.