Worksheet tabs named according to Index worksheet cell values.

[Terminal]

Hi All

I have a workbook used for tracking pupil progress, each tab represents a pupil but they are named generically Pupil1 Pupil2 etc.
The workbook has an index worksheet which has the students names in a column and hyperlinks next to each name linking to a worksheet. The teacher has asked if she can have the worksheets tabs named after each student. As each worksheet is protected she cannot do this manually and this would be a long task for 120 pupils anyway. Would this be possible via VBA\Macro so each year only the Index worksheet would need changing and the tabs would be updated accordingly? Please be gentle with me as it's been 10yrs+ since I've done any vba and that was in Access not Excel

Any help would be much appreciated....

 

[Fluff]

I cant think of any reason, why you would get that behaviour, unless you were also getting an error message.

Also mumps code ran fine for me.
Do the cells from B3 down all have hyperlinks with valid sheet names in col C, or do you have some blanks?
Also do you have any merged cells in there & were any sheet/workbook protection?

 

[Terminal]

I cant think of any reason, why you would get that behaviour, unless you were also getting an error message.

Also mumps code ran fine for me.
Do the cells from B3 down all have hyperlinks with valid sheet names in col C, or do you have some blanks?
Also do you have any merged cells in there & were any sheet/workbook protection?

Hi Fluff

Thank you for the update... No blanks or merged cells, however all worksheets are protected except for the cells that the teacher is suposed to update.
However it appaers you can rename the tabs even though the worksheets are protected as I can do this manullay without unprotecting them... Do you think this may be what is making the code behave oddly?

Many thanks.

 

[Fluff]

If you can change the sheet names manually, then that shouldn't be causing the problem.
Is the Index sheet protected & if so are the cells containing the hyperlinks locked?
Also are any of the student sheets hidden?

 

[Terminal]

If you can change the sheet names manually, then that shouldn't be causing the problem.
Is the Index sheet protected & if so are the cells containing the hyperlinks locked?
Also are any of the student sheets hidden?

Hi Fluff

Yes the Index sheet is protected but the teacher has to unprotect it to reveal\hide\edit the pupils names and also now to rename the tabs.
All Macros are blocked whilst the sheet is protected. The index sheet is the only sheet the teacher has the password for...

This is the VBA I have in the workbook right now...

Sub Reveal_Pupils()
'
' Reveal_Pupils Macro
'

If ActiveSheet.ProtectContents = True Then
    Exit Sub
End If

    Range("B2:B121").Select
    Selection.NumberFormat = "@"
    
End Sub

Sub Hide_Pupils()
'
' Hide_Pupils Macro
'

If ActiveSheet.ProtectContents = True Then
    Exit Sub
End If

    Range("B2:B121").Select
    Selection.NumberFormat = ";;;**"
End Sub

Sub Name_Pupils()

'
' Name_Pupils Macro
'

If ActiveSheet.ProtectContents = True Then
    Exit Sub

   Dim Cl As Range
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Cl.Hyperlinks(1).Follow
      ActiveSheet.Name = Cl.Offset(, 1).Value
      With Cl.Hyperlinks(1)
         .SubAddress = ActiveSheet.Name & "!A1"
         .TextToDisplay = Cl.Offset(, 1).Value
      End With
   Next Cl
End Sub

 

[Fluff]

Are the Pupil sheets hidden?

 

[Terminal]

Are the Pupil sheets hidden?

Hi Fluff

No the pupil sheets are not hidden...
I have copied a snip of the worksheet onto OneDrive so you can see what I am trying to describe...
https://1drv.ms/u/s!AtB8IvWSOXTuj8VKGROGRvkEI2mTTw

Thank you for your help and paitence with this...

 

[Fluff]

I cannot think of any reason why you would get that, unless the hyperlinks were wrong.
Can you supply a copy of the actual workbook? The data can be removed from all the sheets (except the Index sheet), as that should not have any affect on the code.

 

[Terminal]

I cannot think of any reason why you would get that, unless the hyperlinks were wrong.
Can you supply a copy of the actual workbook? The data can be removed from all the sheets (except the Index sheet), as that should not have any affect on the code.

Sure....

The following link will allow access for a few days before expiring and the pupil data has been removed

https://1drv.ms/x/s!AtB8IvWSOXTuj70H1rpxVYP2_KKsfA

It has a password but I will PM it to you [ I am assuming the forum has that feature of course ]

Many thanks again for your help...

 

[Fluff]

Apologies, I should have said a copy of the workbook, BEFORE you tried the macro.

 

[Terminal]

Apologies, I should have said a copy of the workbook, BEFORE you tried the macro.

No problem.... my bad

https://1drv.ms/x/s!AtB8IvWSOXTuj8VOvxJGiyKr8l34AA