I have a shared spreadsheet which has been used for a number of years that has multiple CustomViews (Personal Views for multiple users) applied to it. I'd like to communicate to all of these users as a way to determine which are still in use, or if the users are still using the spreadsheets at all etc.
I'd therefore like to extract the data as list, which should match Outlook usernames and make life easier. All I can find is ways to use the list in a drop down ComboBox, which tells me it's accessible, but I've not got further than this. Happy for it to be a one-time thing, VBA is fine (and I assume necessary). Any ideas?
The list of views used in a workbook can be accessed using the property CustomViews of the workbook. However, the views doesn't provide much usefull information except the name, see the documentation. So it neither tells which sheet will get the active sheet nor which rows/columns are visible or hidden.
You are probably out of look to figure out who created the view or if it is used at all. There is a property Creator for a view object, but that's misleading as it's only an information about which application created the view - it seems that this is needed when running on a Mac, and has always the value 1480803660 (hex 5843454C, or the string XCEL).
The following code will loop over all views of the current workbook and list infos to the immediate window - you can easily adapt this to write it to a sheet, a file or a custom data structure, whatever you need. I use the method Show to activate the view and figure out which sheet is displayed and which range (limited to the used range) is visible.
Sub ListViews()
Dim v As CustomView
For Each v In ActiveWorkbook.CustomViews
v.Show
With ActiveSheet
Dim r As Range
Set r = Intersect(.UsedRange, .Cells.SpecialCells(xlCellTypeVisible))
If r Is Nothing Then Set r = .Cells(1, 1)
Debug.Print v.Name, .Name, r.Address
End With
Next
End Sub