3 Responses to Listing sheet names

1. Harlan Grove says:

   27/08/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

   LikeLike

   Reply
2. Ross says:

   28/08/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

   LikeLike

   Reply
3. dougaj4 says:

   29/08/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

   LikeLike

   Reply

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