Can the Selected Excel Custom View be determined

Question

I know I can show a custom view in excel with vba. What I need to do is determine which custom view was selected by a user, to alter a column header based upon that selection.

They are using this to generate price sheets for customers, many who share the same parameters, but all have different names.

Is there a way to determine the custom view selected? Excel 2003

I changed the title for this question as it more directly asks what I am trying to accomplish

I don't need to trap the event of a CustomView selection, I only need to know which CustomView has been selected. You can determine the Count and lots of other things, but not apparently what is showing in the dropdown of the CustomView CommandBar Control

Answer 1

You can create a class that "hooks" the clicking of the button. Then you can instantiate and destroy the class in the Workbook Activate and Deactivate events. There's three steps:

1) Create a class module called "cCustomViewButton." In the class module paste this code:

Private WithEvents cmdCustomView As Office.CommandBarButton

Private Sub Class_Initialize() '950 is the relevant control ID
Set cmdCustomView = Application.CommandBars.FindControl(ID:=950)
End Sub

Private Sub cmdCustomView_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
CancelDefault = True
msgbox "you'll have to insert your own custom view picker here"
End Sub

2) In a regular module declare a public variable that's an instance of the class you created. You can paste this into any of your regular modules. It needs to be at the top before any routines:

Public clsCustomViewButton As cCustomViewButton

3) Finally you need the code that instantiates and destroys the class. You put it in the ThisWorkbook class. I like to use the Workbook Activate and Deactivate events:

Private Sub Workbook_Activate()
Set clsCustomViewButton = New cCustomViewButton
End Sub

Private Sub Workbook_Deactivate()
Set clsCustomViewButton = Nothing
End Sub

Now just deactivate and then activate the workbook. When you click on the View>Custom Views... button (or use the keyboard shortcuts that click it) the click event will run.

Sadly, you'll see that I substituted the part of the code where we determine the current custom view and replaced it with a messagebox saying that you need to create your own custom view selector form. Originally I just popped up the built-in Custom Views dialog, but alas there seems to be no way to determine the current custom view, so you'll have to offer them the choices and then proceed based on which one they pick.

EDIT:

If you do pursue creating your own custom view picker, here's some basic code examples:

Sub ListCustomViews()
Dim wb As Excel.Workbook
Dim cvCustomView As Excel.CustomView

Set wb = ThisWorkbook
For Each cvCustomView In ThisWorkbook.CustomViews
Debug.Print cvCustomView.Name
Next cvCustomView
End Sub

Sub RenameCustomView()
Dim NewName As String

With ThisWorkbook.CustomViews("Old Name")
    .Show
    NewName = "new Name " & .Name
    .Delete
End With
ThisWorkbook.CustomViews.Add (NewName)
End Sub

EDIT:

I just noticed your question had been updated. Full Disclosure: I never use Custom Views, so hadn't realized there were two types of Custom Views controls - the msoControlButton on the View menu and the msoControlComboBox that you're interested in. Just to be clear, either of these controls can appear both on a toolbar, like Formatting, or in a dropdown menu, such as the View menu.

Happily, the combobox allows you to do what you want. I've modified my original clsCustomViewButton code, which now hooks both types of controls. For the button type, it just refers users to the combobox type. For the combobox type it pops a msgbox with the selected type.

Private WithEvents cmdCustomView As Office.CommandBarButton
Private WithEvents cboCustomView As Office.CommandBarComboBox

Private Sub Class_Initialize() '950 is the relevant control ID
Set cboCustomView = Application.CommandBars.FindControl(Type:=msoControlComboBox, ID:=950)
Set cmdCustomView = Application.CommandBars.FindControl(Type:=msoControlButton, ID:=950)
End Sub

Private Sub cmdCustomView_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
CancelDefault = True
MsgBox "please use the Custom View dialog on the Formatting Commandbar"
End Sub
Private Sub cboCustomView_Change(ByVal Ctrl As Office.CommandBarComboBox)
MsgBox Ctrl.Text
End Sub

You'd probably want to confirm that the dropdown type control exists somewhere, and add it if it doesn't.

For more ideas along this line, read pages 232 onward in Professional Excel Development, a book you'd like!

Absolutely my final EDIT:

If you truly don't need to trap the dropdown change event and just want to be able to determine the current selected view, and assuming there is at least one Custom View dropdown/combobox somewhere in the Excel menus, this line alone will do it:

application.CommandBars.FindControl(type:=msoControlComboBox,Id:=950).text

Whew!