How to know if cell has name

How can I test a cell for a Name, "activecell.name".
In my code below, it will generate an error if a cell is clicked that has no
".Name".
(Option Explicit assumed)

'======
Sub x()
'Begin Registry setting
'Notes:
'100% active on all cells clicked
sSpecLastWs = ActiveSheet.Name
SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
Key:="sSpecLastWs", setting:=sSpecLastWs
sSpecLastCell = ActiveCell.Name 'FAILS HERE
SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
Key:="sSpecLastCell", setting:=sSpecLastCell
'End Registry setting
End Sub
'======
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007

GarysStudent
Fri Mar 14 15:00:01 CDT 2008

Select a cell and run:

Sub nameit()
For Each n In ActiveWorkbook.Names
Set r = Range(n)
If Intersect(ActiveCell, r) Is Nothing Then
Else
MsgBox ("activecell " & ActiveCell.Address & " is part of " & n.Name)
Exit Sub
End If
Next
MsgBox ("activecell " & ActiveCell.Address & " is Nameless")
End Sub

It is important to remember that a cell can have its own name or be part of
a range with a name or be a part of several ranges with different names.
--
Gary''s Student - gsnu200773


"Rick S." wrote:

> How can I test a cell for a Name, "activecell.name".
> In my code below, it will generate an error if a cell is clicked that has no
> ".Name".
> (Option Explicit assumed)
>
> '======
> Sub x()
> 'Begin Registry setting
> 'Notes:
> '100% active on all cells clicked
> sSpecLastWs = ActiveSheet.Name
> SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
> Key:="sSpecLastWs", setting:=sSpecLastWs
> sSpecLastCell = ActiveCell.Name 'FAILS HERE
> SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
> Key:="sSpecLastCell", setting:=sSpecLastCell
> 'End Registry setting
> End Sub
> '======
> --
> Regards
>
> VBA.Newb.Confused
> XP Pro
> Office 2007
>

RickS
Fri Mar 14 15:03:01 CDT 2008

Gary, Thanks!
I definately did not know this:
"It is important to remember that a cell can have its own name or be part of
a range with a name or be a part of several ranges with different names."
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007



"Gary''s Student" wrote:

> Select a cell and run:
>
> Sub nameit()
> For Each n In ActiveWorkbook.Names
> Set r = Range(n)
> If Intersect(ActiveCell, r) Is Nothing Then
> Else
> MsgBox ("activecell " & ActiveCell.Address & " is part of " & n.Name)
> Exit Sub
> End If
> Next
> MsgBox ("activecell " & ActiveCell.Address & " is Nameless")
> End Sub
>
> It is important to remember that a cell can have its own name or be part of
> a range with a name or be a part of several ranges with different names.
> --
> Gary''s Student - gsnu200773
>
>
> "Rick S." wrote:
>
> > How can I test a cell for a Name, "activecell.name".
> > In my code below, it will generate an error if a cell is clicked that has no
> > ".Name".
> > (Option Explicit assumed)
> >
> > '======
> > Sub x()
> > 'Begin Registry setting
> > 'Notes:
> > '100% active on all cells clicked
> > sSpecLastWs = ActiveSheet.Name
> > SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
> > Key:="sSpecLastWs", setting:=sSpecLastWs
> > sSpecLastCell = ActiveCell.Name 'FAILS HERE
> > SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
> > Key:="sSpecLastCell", setting:=sSpecLastCell
> > 'End Registry setting
> > End Sub
> > '======
> > --
> > Regards
> >
> > VBA.Newb.Confused
> > XP Pro
> > Office 2007
> >

RickS
Fri Mar 14 15:07:00 CDT 2008

With Gary's aid:
The macro appears to function like I think it should. ;)
'======
Sub x() 'for all other links
Dim r As Range
'Begin Registry setting
sSpecLastWs = ActiveSheet.Name
SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
Key:="sSpecLastWs", setting:=sSpecLastWs
Set r = Range(ActiveCell.Address)
If Intersect(ActiveCell, r) Is Nothing Then
Else
sSpecLastCell = ActiveCell.Name
SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
Key:="sSpecLastCell", setting:=sSpecLastCell
'End Registry setting
End If
End Sub
'======
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007



"Gary''s Student" wrote:

> Select a cell and run:
>
> Sub nameit()
> For Each n In ActiveWorkbook.Names
> Set r = Range(n)
> If Intersect(ActiveCell, r) Is Nothing Then
> Else
> MsgBox ("activecell " & ActiveCell.Address & " is part of " & n.Name)
> Exit Sub
> End If
> Next
> MsgBox ("activecell " & ActiveCell.Address & " is Nameless")
> End Sub
>
> It is important to remember that a cell can have its own name or be part of
> a range with a name or be a part of several ranges with different names.
> --
> Gary''s Student - gsnu200773
>
>
> "Rick S." wrote:
>
> > How can I test a cell for a Name, "activecell.name".
> > In my code below, it will generate an error if a cell is clicked that has no
> > ".Name".
> > (Option Explicit assumed)
> >
> > '======
> > Sub x()
> > 'Begin Registry setting
> > 'Notes:
> > '100% active on all cells clicked
> > sSpecLastWs = ActiveSheet.Name
> > SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
> > Key:="sSpecLastWs", setting:=sSpecLastWs
> > sSpecLastCell = ActiveCell.Name 'FAILS HERE
> > SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
> > Key:="sSpecLastCell", setting:=sSpecLastCell
> > 'End Registry setting
> > End Sub
> > '======
> > --
> > Regards
> >
> > VBA.Newb.Confused
> > XP Pro
> > Office 2007
> >

Dave
Fri Mar 14 15:42:09 CDT 2008

If you really want to check to see if the activecell has its own name:

Dim myName As Name

Set myName = Nothing
On Error Resume Next
Set myName = ActiveCell.Name
On Error GoTo 0

If myName Is Nothing Then
MsgBox "no name"
Else
MsgBox ActiveCell.Address(0, 0) & vbLf & myName.Name
End If



Rick S. wrote:
>
> How can I test a cell for a Name, "activecell.name".
> In my code below, it will generate an error if a cell is clicked that has no
> ".Name".
> (Option Explicit assumed)
>
> '======
> Sub x()
> 'Begin Registry setting
> 'Notes:
> '100% active on all cells clicked
> sSpecLastWs = ActiveSheet.Name
> SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
> Key:="sSpecLastWs", setting:=sSpecLastWs
> sSpecLastCell = ActiveCell.Name 'FAILS HERE
> SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
> Key:="sSpecLastCell", setting:=sSpecLastCell
> 'End Registry setting
> End Sub
> '======
> --
> Regards
>
> VBA.Newb.Confused
> XP Pro
> Office 2007

--

Dave Peterson

Peter
Fri Mar 14 17:12:27 CDT 2008

Don't see how that helps at all. Try this

Dim nm As Name

On Error Resume Next
Set nm = Nothing ' if necessary reset
Set nm = ActiveCell.Name
On Error GoTo 0

If nm Is Nothing Then
' not named, maybe name it
Debug.Print "not named"
Else
Debug.Print nm.Name, nm.RefersTo
End If

Regards,
Peter T





"Rick S." <RickS@discussions.microsoft.com> wrote in message
news:15E41329-5666-4C41-99AF-00B90003FC00@microsoft.com...
> With Gary's aid:
> The macro appears to function like I think it should. ;)
> '======
> Sub x() 'for all other links
> Dim r As Range
> 'Begin Registry setting
> sSpecLastWs = ActiveSheet.Name
> SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
> Key:="sSpecLastWs", setting:=sSpecLastWs
> Set r = Range(ActiveCell.Address)
> If Intersect(ActiveCell, r) Is Nothing Then
> Else
> sSpecLastCell = ActiveCell.Name
> SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames",
_
> Key:="sSpecLastCell", setting:=sSpecLastCell
> 'End Registry setting
> End If
> End Sub
> '======
> --
> Regards
>
> VBA.Newb.Confused
> XP Pro
> Office 2007
>
>
>
> "Gary''s Student" wrote:
>
> > Select a cell and run:
> >
> > Sub nameit()
> > For Each n In ActiveWorkbook.Names
> > Set r = Range(n)
> > If Intersect(ActiveCell, r) Is Nothing Then
> > Else
> > MsgBox ("activecell " & ActiveCell.Address & " is part of " &
n.Name)
> > Exit Sub
> > End If
> > Next
> > MsgBox ("activecell " & ActiveCell.Address & " is Nameless")
> > End Sub
> >
> > It is important to remember that a cell can have its own name or be part
of
> > a range with a name or be a part of several ranges with different names.
> > --
> > Gary''s Student - gsnu200773
> >
> >
> > "Rick S." wrote:
> >
> > > How can I test a cell for a Name, "activecell.name".
> > > In my code below, it will generate an error if a cell is clicked that
has no
> > > ".Name".
> > > (Option Explicit assumed)
> > >
> > > '======
> > > Sub x()
> > > 'Begin Registry setting
> > > 'Notes:
> > > '100% active on all cells clicked
> > > sSpecLastWs = ActiveSheet.Name
> > > SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames",
_
> > > Key:="sSpecLastWs", setting:=sSpecLastWs
> > > sSpecLastCell = ActiveCell.Name 'FAILS HERE
> > > SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames",
_
> > > Key:="sSpecLastCell", setting:=sSpecLastCell
> > > 'End Registry setting
> > > End Sub
> > > '======
> > > --
> > > Regards
> > >
> > > VBA.Newb.Confused
> > > XP Pro
> > > Office 2007
> > >

Peter
Fri Mar 14 17:17:59 CDT 2008

What I should have done of course, had I seen it, was refer you to Dave's
earlier post, rather than repeat what he had already demonstrated !

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:OE1VMCihIHA.2268@TK2MSFTNGP02.phx.gbl...
> Don't see how that helps at all. Try this
>
> Dim nm As Name
>
> On Error Resume Next
> Set nm = Nothing ' if necessary reset
> Set nm = ActiveCell.Name
> On Error GoTo 0
>
> If nm Is Nothing Then
> ' not named, maybe name it
> Debug.Print "not named"
> Else
> Debug.Print nm.Name, nm.RefersTo
> End If
>
> Regards,
> Peter T
>
>
>
>
>
> "Rick S." <RickS@discussions.microsoft.com> wrote in message
> news:15E41329-5666-4C41-99AF-00B90003FC00@microsoft.com...
> > With Gary's aid:
> > The macro appears to function like I think it should. ;)
> > '======
> > Sub x() 'for all other links
> > Dim r As Range
> > 'Begin Registry setting
> > sSpecLastWs = ActiveSheet.Name
> > SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
> > Key:="sSpecLastWs", setting:=sSpecLastWs
> > Set r = Range(ActiveCell.Address)
> > If Intersect(ActiveCell, r) Is Nothing Then
> > Else
> > sSpecLastCell = ActiveCell.Name
> > SaveSetting appname:="ADM_Spec_Index",
section:="WorkSheetNames",
> _
> > Key:="sSpecLastCell", setting:=sSpecLastCell
> > 'End Registry setting
> > End If
> > End Sub
> > '======
> > --
> > Regards
> >
> > VBA.Newb.Confused
> > XP Pro
> > Office 2007
> >
> >
> >
> > "Gary''s Student" wrote:
> >
> > > Select a cell and run:
> > >
> > > Sub nameit()
> > > For Each n In ActiveWorkbook.Names
> > > Set r = Range(n)
> > > If Intersect(ActiveCell, r) Is Nothing Then
> > > Else
> > > MsgBox ("activecell " & ActiveCell.Address & " is part of " &
> n.Name)
> > > Exit Sub
> > > End If
> > > Next
> > > MsgBox ("activecell " & ActiveCell.Address & " is Nameless")
> > > End Sub
> > >
> > > It is important to remember that a cell can have its own name or be
part
> of
> > > a range with a name or be a part of several ranges with different
names.
> > > --
> > > Gary''s Student - gsnu200773
> > >
> > >
> > > "Rick S." wrote:
> > >
> > > > How can I test a cell for a Name, "activecell.name".
> > > > In my code below, it will generate an error if a cell is clicked
that
> has no
> > > > ".Name".
> > > > (Option Explicit assumed)
> > > >
> > > > '======
> > > > Sub x()
> > > > 'Begin Registry setting
> > > > 'Notes:
> > > > '100% active on all cells clicked
> > > > sSpecLastWs = ActiveSheet.Name
> > > > SaveSetting appname:="ADM_Spec_Index",
section:="WorkSheetNames",
> _
> > > > Key:="sSpecLastWs", setting:=sSpecLastWs
> > > > sSpecLastCell = ActiveCell.Name 'FAILS HERE
> > > > SaveSetting appname:="ADM_Spec_Index",
section:="WorkSheetNames",
> _
> > > > Key:="sSpecLastCell", setting:=sSpecLastCell
> > > > 'End Registry setting
> > > > End Sub
> > > > '======
> > > > --
> > > > Regards
> > > >
> > > > VBA.Newb.Confused
> > > > XP Pro
> > > > Office 2007
> > > >
>
>

Dave
Fri Mar 14 17:28:01 CDT 2008

I think it happens to all of us. Well, I know it happens to me!

Peter T wrote:
>
> What I should have done of course, had I seen it, was refer you to Dave's
> earlier post, rather than repeat what he had already demonstrated !
>
> Regards,
> Peter T
>
> "Peter T" <peter_t@discussions> wrote in message
> news:OE1VMCihIHA.2268@TK2MSFTNGP02.phx.gbl...
> > Don't see how that helps at all. Try this
> >
> > Dim nm As Name
> >
> > On Error Resume Next
> > Set nm = Nothing ' if necessary reset
> > Set nm = ActiveCell.Name
> > On Error GoTo 0
> >
> > If nm Is Nothing Then
> > ' not named, maybe name it
> > Debug.Print "not named"
> > Else
> > Debug.Print nm.Name, nm.RefersTo
> > End If
> >
> > Regards,
> > Peter T
> >
> >
> >
> >
> >
> > "Rick S." <RickS@discussions.microsoft.com> wrote in message
> > news:15E41329-5666-4C41-99AF-00B90003FC00@microsoft.com...
> > > With Gary's aid:
> > > The macro appears to function like I think it should. ;)
> > > '======
> > > Sub x() 'for all other links
> > > Dim r As Range
> > > 'Begin Registry setting
> > > sSpecLastWs = ActiveSheet.Name
> > > SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _
> > > Key:="sSpecLastWs", setting:=sSpecLastWs
> > > Set r = Range(ActiveCell.Address)
> > > If Intersect(ActiveCell, r) Is Nothing Then
> > > Else
> > > sSpecLastCell = ActiveCell.Name
> > > SaveSetting appname:="ADM_Spec_Index",
> section:="WorkSheetNames",
> > _
> > > Key:="sSpecLastCell", setting:=sSpecLastCell
> > > 'End Registry setting
> > > End If
> > > End Sub
> > > '======
> > > --
> > > Regards
> > >
> > > VBA.Newb.Confused
> > > XP Pro
> > > Office 2007
> > >
> > >
> > >
> > > "Gary''s Student" wrote:
> > >
> > > > Select a cell and run:
> > > >
> > > > Sub nameit()
> > > > For Each n In ActiveWorkbook.Names
> > > > Set r = Range(n)
> > > > If Intersect(ActiveCell, r) Is Nothing Then
> > > > Else
> > > > MsgBox ("activecell " & ActiveCell.Address & " is part of " &
> > n.Name)
> > > > Exit Sub
> > > > End If
> > > > Next
> > > > MsgBox ("activecell " & ActiveCell.Address & " is Nameless")
> > > > End Sub
> > > >
> > > > It is important to remember that a cell can have its own name or be
> part
> > of
> > > > a range with a name or be a part of several ranges with different
> names.
> > > > --
> > > > Gary''s Student - gsnu200773
> > > >
> > > >
> > > > "Rick S." wrote:
> > > >
> > > > > How can I test a cell for a Name, "activecell.name".
> > > > > In my code below, it will generate an error if a cell is clicked
> that
> > has no
> > > > > ".Name".
> > > > > (Option Explicit assumed)
> > > > >
> > > > > '======
> > > > > Sub x()
> > > > > 'Begin Registry setting
> > > > > 'Notes:
> > > > > '100% active on all cells clicked
> > > > > sSpecLastWs = ActiveSheet.Name
> > > > > SaveSetting appname:="ADM_Spec_Index",
> section:="WorkSheetNames",
> > _
> > > > > Key:="sSpecLastWs", setting:=sSpecLastWs
> > > > > sSpecLastCell = ActiveCell.Name 'FAILS HERE
> > > > > SaveSetting appname:="ADM_Spec_Index",
> section:="WorkSheetNames",
> > _
> > > > > Key:="sSpecLastCell", setting:=sSpecLastCell
> > > > > 'End Registry setting
> > > > > End Sub
> > > > > '======
> > > > > --
> > > > > Regards
> > > > >
> > > > > VBA.Newb.Confused
> > > > > XP Pro
> > > > > Office 2007
> > > > >
> >
> >

--

Dave Peterson