[Excel] Getting the worksheet name

[Skullmonkey]

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

 

[ivymike]

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.

 

[Skullmonkey]

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

 

[ivymike]

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?

 

[Skullmonkey]

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.

 

[ivymike]

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&quot
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

 

[Skullmonkey]

Thanks for the help, ivy.

 

[moz]

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.

 

[ivymike]

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.