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:
Recent Comments
Blogroll
- 2toria
- AJP Excel Information
- Andrew’s Excel Tips
- Arches
- Bacon Bits
- Best Excel Tutorials
- Beyond Excel
- Colin Caprani
- Contextures Blog
- Cosmic Horizons
- Daily Dose of Excel
- dataprose.org
- Datascopic
- Dick Moffat's Spreadsheet Blog
- EngineerExcel
- Excel Easy
- Excel for Educators
- Excel Unusual
- ExcelGaard
- ExcelXor
- FastExcel
- Grumpy Old Programmer
- In the Dark
- Jan Karel Pieterse
- Jon Peltier’s Blog
- Letters to Nature
- Lucas Beattie
- Math ∩ Programming
- My Engineering World
- Not Only Bridges
- Online Training Hub
- Option Explicit VBA
- Pearson Software Consulting
- Plus Magazine
- Pointy Haired Dilbert
- RAD Excel
- Ron de Bruin
- The Happy Pontist
- The Ken Puls (Excelguru) Blog
- The Math Less Traveled
- TheSpreadsheetGuru
- VBA Projects by Mark Kubiszyn
- VSTO, .NET and Excel
- xll add-in library
- Yoursumbuddy
Family Links
Newton Excel Bach, not (just) an Excel Blog 
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
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
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