Rename Excel Sheet tab???

[TimJr]

Is it possible to rename an Excel sheet tab using a cell value?

 

[Crystalyzer]

This may not be the most elegant way to do it but here goes. Put the following in the Worksheet change event assuming the cell A1 holds the name you want the sheet tab to be.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value <> "" Then
ActiveSheet.Name = Range("A1").Value
End If
End Sub


Thanks and best regards,
-Lloyd

 

[TimJr]

I understand where you are going.
I've worked with Excel for years, but never added VB code to an Excel spreadsheet.

Where would I find the Worksheet change event?

 

[Crystalyzer]

Right click the worksheet tab and select view code. In the drop down that says general select Worksheet. In the drop down next to that select Change. It will start the sub for you.

Thanks and best regards,
-Lloyd

 

[TimJr]

Not working.
This is what I'm doing.
I have a workbook with multiple worksheets.
In worksheet "Names" I enter names of students.
Let's say I entered "Bill Cross" in Names!A3.

In worksheet "Sheet23" I have the formula =Names!A3 in Sheet23!A1.

In Sheet23 I added the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value <> "" Then
ActiveSheet.Name = Range("A1").Value
End If

End Sub

I was hoping when I added Bill Cross in the Names worksheet that tab "Sheet23" would change to "Bill Cross".

What am I doing wrong?

 

[Crystalyzer]

Ok here's what you do

Place the names of your students in column A in the names sheet and make sure that the names sheet is Sheet1. Put the names in rows 1 through (number of names) and place the following code in the worksheet change event of the Names sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 1 Then
Sheets(.Row + 1).Name = Target.Value
End If
End With

End Sub


Thanks and best regards,
-Lloyd

 

[Crystalyzer]

Try this one instead

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 1 And .Row + 1 <= Sheets.Count Then
Sheets(.Row + 1).Name = Target.Value
End If
End With

End Sub

This will only fire when the cell changed is in column A and the row number + 1 is less than or equal to the number of sheets in the worksheet. This way when you change a cell in column A that is in a row greater than the number of sheets in the workbook it wont crash.

Thanks and best regards,
-Lloyd

 

[SkipVought]

TimJr,

Why in the world do you want to chop your data up by yet another data element (Student name)???

What is your objective?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[TimJr]

Individual report cards!

 

[SkipVought]

Report cards are.....

REPORTS!

You don't make a separate files/workbooks/sheets for each report. You select data from your database to report.

All you need is to run a Pivot Table on your student grades table to produce a report for each student for the appropriate marking period, assuming that ALL those data fields are in your table.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Crystalyzer]

Skip,

I agree with you that you don't need to parse your data but (and I mean no slight to ANYONE) I have users who are just NOT comfortable with all the data in one sheet and relying on filters or pivot tables to ensure that the correct data is given to the correct person/entity. It's a comfort level of working with excel issue and not a best practices issue.

I know that I am probably not telling you anything you don't already know. Just offering my 2 cents. Also, I chuckle evertime I see you post this on here, cause I know you must be going out of your mind. I just get the vision of you standing in front of a wall with a post it note on it that says "Bank head here!".



Thanks and best regards,
-Lloyd

 

[SkipVought]

It is a trend I have seen for the past decade.

Data gets chopped up into separate sheets/files.

And then they perform all sorts of acrobatics to get it back together when some inevitable requirement comes down the pike for data aggregation.

For the instances where A is not supposed to see B's data, you take EXTRA SECURITY MEASURES. But, common! "...NOT comfortable with all the data in one sheet..." These are paper and pencil brains, that need to get out of the 1960's and into the new millenium! No nuthin micro-managers!

[tt]
I
D
e
s
c
end.......
[/tt]
feeling much better, I might add. ;-)



Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]