Custom Views Dropdown List
 

I have added the custom views icon to my toolbar.

Is there any way that the dropdown list of custom views can be sorted?

Sincerely

Jim Palmer


--
Jim Palmer
------------------------------------------------------------------------
Jim Palmer's Profile: http://www.excelforum.com/member.php...fo&userid=5210
View this thread: http://www.excelforum.com/showthread...hreadid=380648

Interestingly enough, the lis of Views in the dialog box _does_ get sorted
A-Z, but the combo box on the toolbar retains the Views in their order of
creation. If there is a way to change that I'd would also like to know.

Regards |:)

"Jim Palmer" wrote:


I have added the custom views icon to my toolbar.

Is there any way that the dropdown list of custom views can be sorted?

Sincerely

Jim Palmer


--
Jim Palmer
------------------------------------------------------------------------
Jim Palmer's Profile: http://www.excelforum.com/member.php...fo&userid=5210
View this thread: http://www.excelforum.com/showthread...hreadid=380648

I'd still like to know how the list can be sorted, but now that you
pointed out that the dialog box is sorted I can work with that.

Sometimes the keyboard is faster than the mouse. I just press alt+v
then v to pop up the dialog box, then I can press the first letter of
the view I want to switch to and the cursor jumps to that view.

On the other hand, it would be nice to have a macro that displays the
dialog box. I tried recording one but it just recorded the changing of
the view.

that is
ActiveWorkbook.CustomViews("Balance Sheet").Show

Regards

Jim


--
Jim Palmer
------------------------------------------------------------------------
Jim Palmer's Profile: http://www.excelforum.com/member.php...fo&userid=5210
View this thread: http://www.excelforum.com/showthread...hreadid=380648

You could add a sheet named "Custom Views List", and programmatically
list and sort the view names there. Then, delete and add the views in
alphabetical order. For example:

'=============================
Sub MyCustomViews()
Dim cv As CustomView
Dim wb As Workbook
Dim wsCV As Worksheet
Dim iCV As Integer
Dim rngCV As Range
Dim cCV As Range
Dim bCVPrint As Boolean
Dim bCVRow As Boolean
Dim lCVLast As Long
Dim strCV As String

Set wb = ActiveWorkbook
Set wsCV = wb.Worksheets("Custom Views List")
iCV = 1
strCV = "AllRecords" ' default view

wsCV.Cells.ClearContents

For Each cv In ActiveWorkbook.CustomViews
wsCV.Cells(iCV, 1).Value = cv.Name
iCV = iCV + 1
Next cv

Set rngCV = wsCV.Cells(1, 1).CurrentRegion

lCVLast = rngCV(Rows.Count, 1).End(xlUp).Row

rngCV.Sort Key1:=wsCV.Range("A1"), _
Order1:=xlAscending, Header:=xlNo

'wsCV.Range(wsCV.Cells(1, 1), wsCV.Cells(lCVLast, 1)).Sort _
' Key1:=wsCV.Range("A1"), Order1:=xlAscending, Header:=xlNo
For Each cCV In rngCV
With wb.CustomViews(cCV.Value)
bCVPrint = .PrintSettings
bCVRow = .RowColSettings
.Show
.Delete
End With
wb.CustomViews.Add cCV.Value, bCVPrint, bCVRow
Next cCV

wb.CustomViews(strCV).Show

End Sub
'==================================

Jim Palmer wrote:
I'd still like to know how the list can be sorted, but now that you
pointed out that the dialog box is sorted I can work with that.

Sometimes the keyboard is faster than the mouse. I just press alt+v
then v to pop up the dialog box, then I can press the first letter of
the view I want to switch to and the cursor jumps to that view.

On the other hand, it would be nice to have a macro that displays the
dialog box. I tried recording one but it just recorded the changing of
the view.

that is
ActiveWorkbook.CustomViews("Balance Sheet").Show

Regards

Jim




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html