get the name of an active list

  • Thread starter Thread starter layla
  • Start date Start date
L

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!
 
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
 
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
Click to expand...

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
 
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:
 
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>>
Click to expand...

Thanks,again! I guess I am talking about list of the range names
 
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
 
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 -
Click to expand...

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
 
I think layla should clarify her defintion of a "list"

Is it a Data>List or a Named Range


Gord
 
I'm still confused, too.








--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

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
 
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
 
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 -
Click to expand...

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
 
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)
 
Back
Top