Change Sheet Names Based on Cell Value

[mykulpasskwa]

I have a calculator named "Calc" that is a dashboard for 4 different semesters--Fall, Spring, Summer 1, Summer 2. The main sheet is Calc and each semester has its own datasheet, so there are 5 sheets total.

Depending on what population we're calculating I need the tabs to update accordingly. So, if its changed the semester tab changes to Fall, Winter, Spring, Summer

For example, the standard workbook is the Calc sheet and Fall, Spring, Summer 1, Summer 2. If we click on "Night school" I want the tabs to change to Fall, Winter, Spring, Summer. I have it setup with a formula where if "Night School" is selected then cell G9 changes to "Winter". If G9 changes to Winter, I want the tab to change to Winter. Also, if it is toggled back to Spring, I want it to change back to Spring. Basically I would like it to reflect whatever cell G9 has.

I've looked at a few codes around the internet already, but they keep changing the Calc tab and not the semester that should change.

Anyone have any ideas out there?

 

[Roderick_E]

On the sheet you want this to happen, right click on the tab and VIEW code,

Copy and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$9" Then
If Trim(Range("G9")) <> "" Then
ActiveSheet.Name = Range("G9")
End If
End If
End Sub

This doesn't error trap if you put a bogus name in G9

 

[mykulpasskwa]

On the sheet you want this to happen, right click on the tab and VIEW code,

Copy and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$9" Then
If Trim(Range("G9")) <> "" Then
ActiveSheet.Name = Range("G9")
End If
End If
End Sub

This doesn't error trap if you put a bogus name in G9

Thanks for the response. I'm not having much luck though. I pasted it into the Spring worksheet, which needs to change to Winter when G9 changes from Spring to Winter. When I paste it and run it nothing happens and then if I try to walk through it, it asks which macro I want to run.

 

[Roderick_E]

Thanks for the response. I'm not having much luck though. I pasted it into the Spring worksheet, which needs to change to Winter when G9 changes from Spring to Winter. When I paste it and run it nothing happens and then if I try to walk through it, it asks which macro I want to run.

Hmm, so you went to the Spring worksheet, right click on the tab, VIEW CODE, copy and past in my code. Close.
Go to G9 and change to Winter (or however G9 is changed)???

 

[mykulpasskwa]

Hmm, so you went to the Spring worksheet, right click on the tab, VIEW CODE, copy and past in my code. Close.
Go to G9 and change to Winter (or however G9 is changed)???

Yeah, so that was my mistake. I ended up toying with it until it worked. If I tried to Run Sub it would prompt for a macro which was throwing me off, but if I just changed the G9 cell it worked perfectly. Thanks for the help! Greatly appreciated.