Listing sheet names

Posted on August 26, 2009 by dougaj4

Answering my own question from the previous post, here is a short UDF that will return a list of worksheet names, starting with any chosen sheet:

This entry was posted in Excel, UDFs, VBA and tagged , , , . Bookmark the permalink.

3 Responses to Listing sheet names

  1. Harlan Grove says:
    August 27, 2009 at 7:12 am

    There’s an alternative approach using defined names with some of those names calling XLM functions. See

    http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/e803e69927d7164e

    Like

    Reply
  2. Ross says:
    August 28, 2009 at 6:54 pm

    Hi Doug,
    I think I would have spat it in to one cell, with a “,” between names, what happens if I don’t know how many sheets I have?
    Is it possible to get a UDF to write data to the windows clipboard? That way you could run the function then paste it onto the sheet – if you see what I mean?

    Good stuff
    Ross

    Like

    Reply
  3. dougaj4 says:
    August 29, 2009 at 11:39 am

    Ross – you don’t like array functions?

    The point of the sheetname function was to get all the sheetnames listed in a column, so writing them to a column array seemed like the obvious way to do it.

    I’ve never tried writing to the clipboard before, but looking into it, it seems to have interesting possibilities.

    Everything you need to know here: http://www.cpearson.com/excel/Clipboard.aspx

    The main thing being you need a reference to the Microsoft Forms 2.0 Object Library to make the code below work (I guess you’ll have that anyway, but I’ve just got a new computer and it didn’t have the reference set):

    Function WriteToClip(Textin As String) as string

    Dim mText As DataObject
    Set mText = New DataObject
    mText.SetText Textin
    mText.PutInClipboard
    WriteToClip = Textin & ” copied to clipboard”
    End Function

    Like

    Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.