Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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!

share|improve this answer
    
This looks promising. I have figured out that I can get close with currentvw = ThisWorkbook.CustomViews.Item(index).Name but as you mention, no idea how to figure out the index. There are dozens in this sheet and if I could document each one's index and do a select case that might do it, you gave me lots to go with thanks –  datatoo Sep 30 '11 at 7:36
    
gosh a custom picker might be hell to do with the way custom views are named and defined –  datatoo Sep 30 '11 at 8:11
    
I'm not sure what issues you're seeing. In theory it seems fairly straightforward. I added a couple of sample routines. Let me know what you think. –  Doug Glancy Oct 1 '11 at 16:33
    
I noticed you'd changed your question and think I've come up with a way to actually determine the chosen Custom View. –  Doug Glancy Oct 1 '11 at 17:48
    
wish I could mark this up further, you have given me so much help. You got me where i was trying to go, Thanks. –  datatoo Oct 2 '11 at 11:32
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.