Worksheet VBA naming

[vossler]

I have a summary worksheet from which I have student names that are hyperlinked to their own worksheet which has student grades on it. My problem is that I would like to change the hyperlink labeled "student 1" on the summary page, which is the default name when opening the workbook from the template, to the actual student name. After doing so it should then take me to that worksheet and place that name in cell A1. It should also change the name of the worksheet to the student name. Here is the code I currently have that does in fact change the worksheet name but then upon coming back to the summary hyperlink won't link back to the same page. It won't recognize the new name and links me to the summary by default. BTW, the student worksheets A1 cell is referenced to the summary worksheet hyperlinked cell.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then Sh.Name = Target
End Sub

What's wrong? I did not develop this code, it was given to me by someone else. I say this only to let you know I'm not a VBA programmer.

Thanks!

[johnske]

Hi vossler, welcome to VBAX

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then ActiveSheet.Name = Target
End Sub

HTH,
John

[vossler]

Thanks for welcome and quick response. I never thought I'd see myself writing in to a programmers board.

Your help is greatly appreciated John, I'll try your recommendation tomorrow when I get to work. I'll let you know how it went. Also thanks for telling me how to post VBA code so that it is easier for folks to read.

[johnske]

Hi vossler,

Not a prob. By the way, that bit goes in the relevant worksheet module. (just read your last sentence ) - also - your original code belongs in the ThisWorkbook module and it should work fine in there (it seems strange it doesn't)... is that where you had it?

Regards,
John

[vossler]

John,

I installed the code but it had no effect on the problem and didn't appear to really do anything.

Yes, I had the original code in the ThisWorkbook Module. I installed your code in what I thought was a specific worksheet module (I clicked on a worksheet and then pasted the code) but this installed in all worksheets. I must not be aware of how to install something in the worksheet module.

Thank again,
Reno

[johnske]

Hi Reno,

Can you copy the workbook, remove any sensitive info (and only need 3 or 4 sheets), zip it and post it here for us to look at? I'm not getting this problem...

Use 'Go Advanced' and scroll down a bit to find a 'Manage Attachments' button (to upload the zip file)

Regards,
John

[vossler]

I've enclosed the file in question, it's a template so it doesn't have any data.

Thanks,

Reno

[MWE]

I took a quick look at the spreadsheet. The Student names in the relevant A1 cells are linked by to the Summary sheet. So if a hyperlink on the Summary sheet is edited (right click | Hyperlink | Edit Hyperlink | Text to display) and the actual student's name used for the link text, the first part of the problem is resolved.

One would think that Johnske's suggestion of using the sheet change procedure would be a simple way to rename the activesheet to whatever is found in cell A1. However, when the hyperlink executes, nothing on the target sheet is changed and, thus, the Worksheet_Change does not trigger (at least not in Excel2000). I Have been meaning to post a query about this type of problem, i.e., how to execute a sheet-specific macro when that sheet is the target of a hyperlink.

Another solutino to Vossler's problem would be a single macro that sweeps through all sheets starting with, say, "Student" and changes the name of the sheet to whatever is found in cell A1. A command button could be placed on the summary sheet and linked to the relevant macro.

[Ken Puls]

how to execute a sheet-specific macro when that sheet is the target of a hyperlink.

Hi MWE!

Maybe check out the FollowHyperlink or SheetFollowHyperlink events. (Sheet/ThisWorkbook respectively). (in 2003 anyway.)

HTH,

[vossler]

MWE, Yes this is exactly what happens for me. You can force the worksheet to change it's name by double clicking on cell A1, but when you go back to the summary sheet and attempt to click on the link it tells you "Reference is not valid".

[Bob Phillips]

Try this.

[vossler]

Thanks XLD, It works like a charm.


Now I'm going to have to take a look under the hood and see just how you did that. That ought to be fun!

Thanks to everyone who took a look and helped out. I know that I certainly couldn't have done it on my own.

[MWE]

Thanks XLD, It works like a charm.


Now I'm going to have to take a look under the hood and see just how you did that. That ought to be fun!

Thanks to everyone who took a look and helped out. I know that I certainly couldn't have done it on my own.

I must have missed the intent of the problem. xld's version (as it runs on my system: Excel2000 under WinXP) does not appear to solve any of the problem(s) originally posed. My interpretation of what was required:
1. post the name in the hyperlink anchor cell to cell A1 of the hyperlink target sheet
2. rename the target sheet to the name in the hyperlink target
3. (as result of renaming the target sheet) rebuild the hyperlink so that it points to a valid sheet

[vossler]

MWE,

Your 3 point assessment is right on, those are the requirements. After receiving the solution I sent it to my boss (Win XP and Excel 2000) and it didn't work completely as intended but he was able to do the renaming as described in your three points. Other parts of the workbook however didn't work as it did on my system (Win XP Excel 2003). I guess there are some anomalies between the different versions of Excel.

[Bob Phillips]

I must have missed the intent of the problem. xld's version (as it runs on my system: Excel2000 under WinXP) does not appear to solve any of the problem(s) originally posed.

That too was my understanding of the problem.

Are you saying that it doesn't rname the sheet, doesn't put the new name in the A1 cell on that sheet, and doesn't redirect the hyperlink?

If so, what does it do on your system?

[Bob Phillips]

Just tried it on 2000, and in essence it works okay for me there too.

Some of the sheets and names seem to have gotten mixed up in tyhe posted version, but I was able to fix these by changing the name back to the same as the sheet name, then change again to the base name, forcing correction.

I think this version is now correct in 2000 and XP.