×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

[Excel] Getting the worksheet name

[Excel] Getting the worksheet name

[Excel] Getting the worksheet name

(OP)
I'm working on scripting an small VBA project for Excel2000 that will graph preknown data points from a dynamic amount of data sheets.  Since the data sheets will be entered by the end-user in an unknown order, the code must retrieve the name of the worksheet for use as a title for the data in the Chart Legend.  

Q: How can I get the name of the worksheet from Excel?

Also, can anyone think of a good method for keeping track of the sheet nums or names?  To clarify: if the end-user fumbles with a worksheet and deletes it instead of clearing it and re-entering data, the worksheets may now be numbered Sheet1, Sheet2, Sheet4, etc.  A For-loop built on a counter would not work in this case.  At least, not that I have been able to ration.

Thanks for any help someone can lend.
SM

RE: [Excel] Getting the worksheet name

as far as looping through the worksheets goes, you have a couple of choices, and the following example code should help you with both (also how to get the name):

Sub testes2()
    Dim MyWS As Excel.Worksheet
    Debug.Print Worksheets.count
    For Each MyWS In Worksheets
       Debug.Print MyWS.Name
    Next
End Sub

If your active workbook is a default (blank) one, and if you have the "immediate" window open, you'll see the following output:

 3
Sheet1
Sheet2
Sheet3

Anyway, the two methods that come to mind are:

1) set a variable (numofsheets) to worksheets.count, which gives you the number of worksheets, then loop from worksheets(1) to worksheets(NumOfSheets)
 such as:

numofsheets = worksheets.count
for x = 1 to numofsheets
    worksheet(x).activate
    dowatchalike x, "fun, isn't it?"
next x

2) use a For Each loop to go through each worksheet (as in the first set of code)

post if you need more (unclear) help.   :P




RE: [Excel] Getting the worksheet name

(OP)
What I was really looking for was to be able to get the tab name of the worksheet.  Here's a snippet:


ActiveChart.SeriesCollection(1).Name =
???


Where "???" is the name of the worksheet tab that the data was pulled from. ActiveSheet.Name won't work because the activated sheet is really the Chart that I'm in the middle of configuring.

Thanks for the other stuff, though.  It's useful.

SM

RE: [Excel] Getting the worksheet name

When the data is "grabbed" from a worksheet, is it done automatically or by the user?

Are you trying to find what sheet the data in an existing chart came from, based on the info in the Xvalues for the chart?




RE: [Excel] Getting the worksheet name

(OP)
The data is automatically taken from the sheet by the macro.  Whilst taking the data from the worksheet, I'm attempting to also find out the name of the worksheet that it's coming from.  

RE: [Excel] Getting the worksheet name

then I would think that it would be fairly easy - since when you go to get the data from the worksheet, you know which worksheet it is, just grab its name at the same time.

example:

Sub testy()
    Dim TabName As String, MyChartName As String
    Dim MySheet As Excel.Worksheet, xlsChart As Excel.Chart
    Dim xvals As String, yvals As String
    
    For Each MySheet In Worksheets
        TabName = MySheet.name
        MyChartName = TabName
        Set xlsChart = MySheet.ChartObjects.Add(50, 50, 600, 400).Chart
        With xlsChart
            .ChartType = xlXYScatterSmoothNoMarkers
            .SetSourceData Source:=Sheets(TabName).Range("B3:C6")
            xvals = "=" + TabName + "!B3:B6"
            yvals = "=" + TabName + "!C3:C6"
            .SeriesCollection(1).name = TabName
            .HasTitle = True
            With .ChartTitle
                .Characters.Text = "Chart title is: " + TabName
                .Characters.Font.Size = 12
            End With
            .Axes(xlValue, xlPrimary).TickLabels.Font.Size = 12
            .Axes(xlCategory, xlPrimary).TickLabels.Font.Size = 12
            .Legend.Font.Size = 12
        End With
    Next
End Sub

RE: [Excel] Getting the worksheet name

(OP)
Thanks for the help, ivy.

RE: [Excel] Getting the worksheet name

Maybe I missed some go-between here, but I don't see the connect between the last response and the original question!?  (call me stoopid ... but why confuse someone with stuff on charts?).  For skullmonkey - as I understand your original question, you just want to know how to get the sheet names ... it's very simple: just reference the active sheet during a loop and use the "name" property like this:

Private Sub GetSheetNames()
Dim sht As Worksheet
    For Each sht In Worksheets
        MsgBox (sht.Name)
    Next sht
End Sub

This will display the sheet name is a message box during the loop, but you get the idea ....

Hope this helps,
Moz.

RE: [Excel] Getting the worksheet name

Your suggestion is a whole lot like one of the two examples in the first response, isn't it?  

The reason we got onto the "confusing" chart stuff was that Skullmonkey said (after reading an example similar to yours)

"ActiveSheet.Name won't work because the activated sheet is really the Chart that I'm in the middle of configuring."  

I figured that he might like an example of how to temporarily store the sheet name for later use on a chart.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources