Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know if cell has name
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know if cell has name
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know if cell has name
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know if cell has name
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know if cell has name
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." wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know if cell has name
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 ... 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." wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to know if cell has name
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 ... 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." wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
NEED VBA TO SELECT A CELL; NOTE THE CELL VALUE;COPYADJ CELL;FIND CELL VALUE IN A RANGE AND SO ON | Excel Programming | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) |