get the name of an active list

[layla]

Hi,
How can I implement this psedue code:assume: a cell is activated(can
be any where in the sheet)
if (active.cell is in a list) then alist=get the name of the
list ....end if
Thanks!

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim myListName As String

myListName = ""
On Error Resume Next
myListName = ActiveCell.ListObject.Name
On Error GoTo 0

If myListName = "" Then
'not in a list
Else
MsgBox myListName
End If
End Sub

 

[layla]

Option Explicit
Sub testme()

    Dim myListName As String

    myListName = ""
    On Error Resume Next
    myListName = ActiveCell.ListObject.Name
    On Error GoTo 0

    If myListName = "" Then
        'not in a list
    Else
        MsgBox myListName
    End If
End Sub

Thanks! But when I apply your code instead of the list name(i.e.
myList),I get "list1" which is strange,because I do not have that in
my list names

 

[Dave Peterson]

I don't understand.

Are you talking about xl2003's List (Data|List|Create list) object?

Or maybe you're talking about a range name?

Which one did you really want to see?

layla wrote:

 

[Dave Peterson]

ps.

Insert|Name

is one way of defining a range name.

layla wrote:

 

[layla]

I don't understand.

Are you talking about xl2003's List (Data|List|Create list) object?

Or maybe you're talking about a range name?

Which one did you really want to see?

layla wrote:

<<snipped>>

Thanks,again! I guess I am talking about list of the range names

 

[Gord Dibben]

If you have no Data>List>Create List object then you should not get a msgbox
return of "List1"

i.e. if no Data>List exists or you have not selected a cell within that
List the macro should simply quit.


Gord Dibben MS Excel MVP

 

[layla]

If you have no Data>List>Create List object then you should not get a msgbox
return of  "List1"

i.e.  if no Data>List exists or you have not selected a cell within that
List the macro should simply quit.

Gord Dibben  MS Excel MVP




- Show quoted text -

Thanks! what I need to do is this: In a sheet with more than one
list,if the curser is in a list,means that a list is active.I need to
know the name of the active list

 

[Dave Peterson]

That's what the code in the first suggestion gave you.

 

[Gord Dibben]

I think layla should clarify her defintion of a "list"

Is it a Data>List or a Named Range


Gord

 

[Dave Peterson]

I'm still confused, too.

I think layla should clarify her defintion of a "list"

Is it a Data>List or a Named Range

Gord

 

[layla]

I'm still confused, too.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks again for your resopnds! by list I mean static type of list
(data>list>create list...then..insert>name>define)
my problem is : if an active cell is in a list ,automatically it
activates the list.how can I write a code to do this:
if ( a list is active) then do

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim TestListObj As ListObject

Set TestListObj = Nothing
On Error Resume Next
Set TestListObj = ActiveCell.ListObject
On Error GoTo 0

If TestListObj Is Nothing Then
MsgBox "not in a list"
Else
MsgBox "It's in a list named: " & TestListObj.Name
End If

End Sub

 

[layla]

Option Explicit
Sub testme()
    Dim TestListObj As ListObject

    Set TestListObj = Nothing
    On Error Resume Next
    Set TestListObj = ActiveCell.ListObject
    On Error GoTo 0

    If TestListObj Is Nothing Then
        MsgBox "not in a list"
    Else
        MsgBox "It's in a list named: " & TestListObj.Name
    End If

End Sub







--

Dave Peterson- Hide quoted text -

- Show quoted text -

appreciate! However,every time the code is running ,even when the this
is not active, it automatically activate the list, do you have any
idea why

 

[layla]

ps.

Insert|Name

is one way of defining a range name.

layla wrote:

I find the bug! your code works,it was some logical error! thanks!

 

[Dave Peterson]

Where's the activecell? Are you sure it's not in a list?

If you get the name of the list when you run this, then I bet that activecell is
in a list.

You could change the msgbox to:

msgbox "Location: " & activecell.address(external:=true) _
& vblf & testlistobj.name _
& vblf & testlistobj.range.address(external:=true)