1

Excel now has the possibility to store personal filtering views to help collaboration in simultaniously used documents. I could only find Microsoft documentation for an add-in, but the function is available in my Excel version of MS Excel for Microsoft 365 MSO (16.0.13127.20266) 32bit.

https://learn.microsoft.com/en-us/javascript/api/excel/excel.namedsheetview?view=excel-js-preview

I am trying to store the currently applied NamedSheetView name property (for later restoring option) but this code fails:

Dim sh1 As Worksheet
Dim xViewName As String
Set sh1 = ThisWorkbook.Sheets(Sheet6.Name)
xViewName = sh1.NamedSheetView.Name

However this code works (with previously created "Test" view):

sh1.NamedSheetViews.GetItem("Test").Activate

If this NamedSheetViews is a collection, I should be able to get the item property, but these codes also fail:

strName = sh1.NamedSheetViews.GetItem(1).Name
strName = sh1.NamedSheetViews.Item(1).Name

Anyone has ever succeeded in getting the current NamedSheetView of a Worksheet?

LegrA
  • 41
  • 4

3 Answers3

1

Here is how I probe unknown Object properties:

Testing Method

I start with a reference to the Object. If I don't know what the Object is I use TypeName() to return it's class name (data type). I then declare a variable of that data type. Wash, rinse and repeat as I drill down the structure. Once the variable is declared, selecting the variable and pressing F1 with open the Microsoft Help document for that data type.

Module Code

Sub WhatIsThat()
    Const TestName As String = "TestName"
    Dim View As NamedSheetViewCollection
    Set View = Sheet6.NamedSheetViews
    
    On Error Resume Next
    View.GetItem(TestName).Delete
    On Error GoTo 0
    
    View.Add TestName
    Dim SheetView As NamedSheetView
    Dim n As Long
    For n = 0 To View.Count - 1
        Debug.Print View.GetItemAt(n).Name
        Set SheetView = View.GetItemAt(n)
        Debug.Print SheetView.Name
    Next
    Stop
End Sub

Immediate Window Tests

?TypeName(Sheet6.NamedSheetViews)
?View.GetItemAt(0).Name
?TypeName( View.GetItemAt(0))
TinMan
  • 6,624
  • 2
  • 10
  • 20
  • Thank you TinMan, this was a great help. Now I am able to iterate through the collection. I am still faced with the big question: how can I identify the active NamedSheetView - the one currently active on the sheet - if any. Unfortunately the NamedSheetView - just as the collection - only has Parent, Count, Name and Application properties. Unfortunately I could not find any trace of what NamedSheetView is curretly applied among the Sheet properties either. Any advice how to identify the currently selected NamedSheetView? – LegrA Nov 26 '20 at 07:32
  • I was only able to find Javascript API code: GetActive() https://learn.microsoft.com/en-us/javascript/api/excel/excel.namedsheetviewcollection?view=excel-js-preview – LegrA Nov 26 '20 at 07:42
  • FOUND IT! Dim SheetView As NamedSheetView Dim sh1 As Worksheet Set sh1 = ThisWorkbook.Sheets(Sheet6.Name) Set SheetView = sh1.NamedSheetViews.GetActive – LegrA Nov 26 '20 at 07:46
  • @LegrA Way to go! – TinMan Nov 26 '20 at 09:01
0

SOLUTION: (Thanks for the great help from TinMan)

Dim SheetView As NamedSheetView 
Dim sh1 As Worksheet
Dim ActiveSheetView as string 
Set sh1 = ThisWorkbook.Sheets(Sheet6.Name) 
Set SheetView = sh1.NamedSheetViews.GetActive
ActiveSheetView = SheetView.Name

Application:

sh1.NamedSheetViews.GetItem(ActiveSheetView).Activate
LegrA
  • 41
  • 4
0

To solve this problem I record a Macro With the views I have in my workbook and it works and came out to simpler than I thought so. It is as follows:

 Sub ViewPractice()
'
' ViewPractice Macro
'
ActiveWorkbook.CustomViews("Setup").Show
ActiveWorkbook.CustomViews("DashBoard").Show
End Sub
Jose
  • 1
  • 1