Refresh worksheet tab name

[mykulpasskwa]

Hi all. People have been super helpful so far helping me with this project I'm working on, but I have one more issue.

I have 5 worksheets and the first one is a dashboard calculator of sorts called "Calc". I have a checkbox that when toggled changes the tab name on four other worksheets in their respective G9 cell. The problem is that in order to actually change the tab name you have to click on each worksheet individually and then change the active cell and then it updates to the new name.

I'm wondering if there is way that when the macro runs it automatically selects a new cell in each workbook but returns to the dashboard or if there is a way to refresh the workbook after the macro runs? Below is a sample of the code that changes the name of the cell.

Any help is greatly appreciated!

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Set Target = Range("G9")
    If Target = "" Then Exit Sub
    On Error GoTo Badname
    ActiveSheet.Name = Left(Target, 31)
    Exit Sub
Badname:
    MsgBox "Please revise the entry in G9." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
    Worksheets("Fall").Range("E12").Activate
    ActiveSheet.EnableCalculation = False
    ActiveSheet.EnableCalculation = True
End Sub

 

[Jerry Sullivan]

Hi mykulpasskwa,

I've read your earlier post to better understand what you want to do.
https://www.mrexcel.com/forum/excel...based-cell-value-post5060112.html#post5060112

If I'm understanding things, I'd suggest not use separate Worksheet_SelectionChange procedures on each sheet, but instead have one procedure that is called when the checkboxes are changed on the Calc sheet. That one procedure would read the currently selected checkbox, then for each of the four sheets, it would change the value in G9 and the sheetname based on the mapping for that population.

To use the code below...

1. Delete all your existing Worksheet_SelectionChange code.

2. Change the CodeName of each of your worksheets to wksCalc, wksSemester1, wksSemester2, wksSemester3, and wksSemester4. You can edit the CodeNames in the VB Editor by selecting the worksheet then editing its Name in the Properties window.

3. Paste the code below into a Standard Code Module (like Module1)

4. Edit the address "A2" to the actual cell address on sheet Calc that holds the value of the selected Population.

Sub RenameSheets()
 Dim lNdx As Long
 Dim sPopulation As String, sNewSheetName As String
 Dim vWorksheetArray As Variant, wks As Variant
 Dim vWorksheetNames As Variant
 
 vWorksheetArray = Array(wksSemester1, wksSemester2, wksSemester3, wksSemester4)
 
 '--read the value on the calc sheet linked to the checkbox
 '--modify A2 to actual cell address
 sPopulation = wksCalc.Range("A2").Value
 
 '--get mapping for sheet names
 Select Case sPopulation
   Case "Night School"
      vWorksheetNames = Array("Fall", "Winter", "Spring", "Summer")
   Case Else  'standard default
      vWorksheetNames = Array("Fall", "Spring", "Summer1", "Summer2")
 End Select
 
 '--assign temporary names to avoid duplicate names errors
 For Each wks In vWorksheetArray
   lNdx = lNdx + 1
   wks.Name = "TempName" & CStr(lNdx)
 Next wks
 
 '--reset counter
 lNdx = LBound(vWorksheetArray) - 1
 
 For Each wks In vWorksheetArray
   lNdx = lNdx + 1
   sNewSheetName = vWorksheetNames(lNdx)
   wks.Range("G9").Value = sNewSheetName
   wks.Name = sNewSheetName
 Next wks

End Sub

Run the macro RenameSheets from the Macro dialog (Alt-F8). Once you have that running the way you want it, we can add some code to call RenameSheets when your checkbox values are changed.

 

[mykulpasskwa]

After tinkering around with the cell references it works like a charm. This is perfect, Jerry. Thank you so much!