I use very large spreadsheets with custom views. However, I can't find a way
to reference the names of the views in formulas, etc. Is there a way that
I'm overlooking or is it impossible to use the names except with the View -
Custom Views dialog box? I would prefer to do it in a worksheet formula but
VBA would be ok.

A Custom View is stored information about your worksheet settings, and
you can't refer to it in a formula.

You could name a range, and refer to that in the formula. There are
instructions in Excel's Help, and he

http://www.contextures.com/xlNames01.html

Th10ECn wrote:
I use very large spreadsheets with custom views. However, I can't find a way
to reference the names of the views in formulas, etc. Is there a way that
I'm overlooking or is it impossible to use the names except with the View -
Custom Views dialog box? I would prefer to do it in a worksheet formula but
VBA would be ok.


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

I don't think you can determine the name of the current view.

Th10ECn wrote:

I use very large spreadsheets with custom views. However, I can't find a way
to reference the names of the views in formulas, etc. Is there a way that
I'm overlooking or is it impossible to use the names except with the View -
Custom Views dialog box? I would prefer to do it in a worksheet formula but
VBA would be ok.

--

Dave Peterson

Hi,

how about getting the current view name from a built-in control.
I have not done enough testing but this seems to work (in Excel 2000)

Function CurrentView(Optional DummyArg As Variant) As String
Dim cbo As CommandBarComboBox
On Error Resume Next
Set cbo = Application.CommandBars.FindControl(ID:=950)
On Error GoTo 0
If cbo Is Nothing Then
With Application.CommandBars.Add(Temporary:=True)
Set cbo = .Controls.Add(ID:=950)
.Enabled = False
End With
End If
CurrentView = cbo.Text
End Function

--
HTH,

okaizawa


Th10ECn wrote:
I use very large spreadsheets with custom views. However, I can't find a way
to reference the names of the views in formulas, etc. Is there a way that
I'm overlooking or is it impossible to use the names except with the View -
Custom Views dialog box? I would prefer to do it in a worksheet formula but
VBA would be ok.

It looks like that this is picking up the first name in the list.

That view may not be the current view.

And if I showed that view, then changed some settings (unhid columns), the
builtin dialog didn't change (the top item was still selected).

okaizawa wrote:

Hi,

how about getting the current view name from a built-in control.
I have not done enough testing but this seems to work (in Excel 2000)

Function CurrentView(Optional DummyArg As Variant) As String
Dim cbo As CommandBarComboBox
On Error Resume Next
Set cbo = Application.CommandBars.FindControl(ID:=950)
On Error GoTo 0
If cbo Is Nothing Then
With Application.CommandBars.Add(Temporary:=True)
Set cbo = .Controls.Add(ID:=950)
.Enabled = False
End With
End If
CurrentView = cbo.Text
End Function

--
HTH,

okaizawa

Th10ECn wrote:
I use very large spreadsheets with custom views. However, I can't find a way
to reference the names of the views in formulas, etc. Is there a way that
I'm overlooking or is it impossible to use the names except with the View -
Custom Views dialog box? I would prefer to do it in a worksheet formula but
VBA would be ok.

--

Dave Peterson