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!
