Search code examples
excelvbaoffice365class-properties

How to get the name property of the active NamedSheetView class?


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?


Solution

  • 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