Code To Scroll Worksheet

[Spencer]

My worksheet (Sheet1) has Freeze Panes set so that rows 1-5 and columns A & B are always showing. So, cell C6 is the first cell in the upper left-hand corner that you can scroll to.

I want to hyperlink to that cell C6 from another worksheet (Sheet2), so that when that destination worksheet is first made active the worksheet will scroll to show all the data. In other words, if the last time someone was on Sheet 1 the were working in cell ZZ99, the worksheet will scroll as far up and left as it can when it opens, instead of staying on cell ZZ99.

This would not be a problem except for three things. My Sheet1 is protected, cell C6 is locked, and the protection settings do not allow users to select locked cells. I can't change any of those three things, so I need another solution to get the worksheet to scroll all the way up and all the way to the left so cell C6 is visible as soon as the Sheet1 is activated.

I've attached a simple spreadsheet example.

Any help would be appreciated.

[Andy Pope]

paste into Sheet2 object

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Rather than a hyperlink which won't work since C6 is locked, why not have a button linked to the following Sheet Activate event.

Please Login or Register  to view this content.

The cursor won't be in C6 since you don't permit that in the sheet protection settings but C6 will be visible with the cursor in D6.

Regards

[Spencer]

paste into Sheet2 object

Please Login or Register  to view this content.

I think this is exactly what I need. It looks like I can even enter another line of code to activate a certain cell in Sheet1, which kind of kills two birds with one stone.

Thank you,
Spence

[Spencer]

Hi,

Rather than a hyperlink which won't work since C6 is locked, why not have a button linked to the following Sheet Activate event.

Please Login or Register  to view this content.

The cursor won't be in C6 since you don't permit that in the sheet protection settings but C6 will be visible with the cursor in D6.

Regards

Richard, thank you for the advice. I think the other solution that Andy suggested will be the easiest for me to implement, as I have 25 worksheets to apply this to.

Thanks, Spence

[Andy Pope]

If you have other hyperlinks then you might be better of using the Thisworkbook event

Please Login or Register  to view this content.

[Spencer]

[QUOTE=Andy Pope;2494062]If you have other hyperlinks then you might be better of using the Thisworkbook event

Do you mean other hyperlinks on the same worksheet, or other hyperlinks on other worksheets within the same workbook?

I definately have other hyperlinks on other worksheets in the same workbook, but right now the 25 worksheets I am putting this code on only have 1 hyperlink each.

The sheet that all these hyperlinks lead to will end up having many hyperlinks. Those hyperlinks will take the user back to the individual 25 sheets.

[Andy Pope]

If you have multiple hyperlinks to the sheet with the locked cell then you can use the event in the Thisworkbook object as they will all fire that event.
Of course you may need to add code to check hyperlinks that you do not want to react to otherwise all links will end up at the same place.

[Spencer]

The solution that Andy provided me works great, but I seem to have one quirk. If the cell that the hyperlink is in is currently selected and I click somewhere out on a locked cell (like a blank part of the worksheet whose cells are locked), the hyperlink gets activated.

I can live with this, but it will probably mess with a lot of my co-workers minds.

If there is a solution, please let me know.

Thanks, Spence

[Andy Pope]

I can not replicate from your description. Can you post example file.