[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
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
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
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
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
RE: [Excel] Getting the worksheet name
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
RE: [Excel] Getting the worksheet name
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
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.