• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Retrieve the names of worksheets and display them in a MsgBox

Dear Experts:

On the currently open workbook I would like to perform the following action using VBA:

Retrieve the worksheets' names of all the worksheets of the currently open workbook with  the exception of the sheets named 'Main', 'Calculation' and any sheets with the default names such as 'sheet1'.

The worksheet names are to be displayed in a MsgBox. I know that there is a character limitation on MsgBoxes.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
AndreasHermle
Asked:
AndreasHermle
  • 2
  • 2
2 Solutions
 
Martin LissRetired ProgrammerCommented:
Dim ws As Worksheet
dim strNames as String

For Each ws In worksheets
    If Left(ws.Name,5) = "Sheet" or ws.Name = "Main" or ws.Name = "Calculation" Then
        ' do nothing
    Else
        strNames = strNames & ws.Name & " "
    End If
Next

MsgBox strNames

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
Sub listsheets()
    Dim ws As Worksheet
    Dim wslist As String
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> ws.CodeName And ws.Name <> "Main" And ws.Name <> "Calculation" Then
            wslist = wslist & IIf(wslist = "", "", vbCrLf) & ws.Name
        End If
    Next ws
    MsgBox wslist
End Sub
0
 
AndreasHermleAuthor Commented:
Hi ssaqibh,

the sheet names are nicely listed in your msgbox although I am afraid to tell you that the msgbox still lists 'Sheet1'. ws.CodeName does not seem to work.

Martin: Great job. Works just fine. Thank you very much.


Regards, Andreas
0
 
AndreasHermleAuthor Commented:
Dear both,

great job from both of you. By combining the codes I am getting exactly the result I was looking for.

Regards, Andreas
0
 
Martin LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now