Custom View Names

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 here:

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