Hyperlink to a cell - not a simple solution

[mattyn]

All - Hi!

I know how to add a hyperlink to send the cursor to a particular cell. However I need to position that cell in a certain location on the screen.

The spreadsheet is really wide - and incorporates a Freeze Panes to ease horizontal scrolling. When I hyperlink to the first cell in the section I want, the cell is at the far right of the screen - I want the hyperlinked cell to be scrolled against the freeze pane line.

Does that make sense - if so, how do I do this?

TIA

Matt

 

[Tim_Excel_]

Hi,

I'm thinking the ActiveWindow.ScrollRow would fit your needs

Sub theyseemescrolling()
scrolltorow = ThisWorkbook.Sheets(1).Range("A60").Row

ThisWorkbook.Sheets(1).Activate 'edit
ActiveWindow.ScrollRow = scrolltorow


End Sub

If you edit variable scrolltorow to whatever's the cell reference you're using, this code scrolls to that cell's row, meaning that cell will be on the top of the page, against the freeze panes line.

 

[mattyn]

Thanks Tim - but I don't quite understand what I need to edit.

Assume the sheet name on the tab is called Progress, but it is actually Sheet 3 on the workbook. And for a particular link, I need to go to cell GQ2.

Do I need to edit Sheets(1) and Range ("A60")?

Matt

 

[Tim_Excel_]

That's correct. You have to make sure the code is referring to 1) the correct workbook, 2) the correct sheet and 3) the correct cell.

Furthermore this code makes sure you can use a variable range reference. For example, you may have something like

Sub theyseemescrolling()
Dim cref as Range
cref = TextBox1.Value 'this is a variable range reference
scrolltorow = ThisWorkbook.Sheets(1).Range(cref).Row

ThisWorkbook.Sheets(1).Activate 'edit
ActiveWindow.ScrollRow = scrolltorow

[COLOR=#333333]End Sub[/COLOR]

However, if you're always going to use cell GQ2, then you may as well use

Sub theyseemescrolling()
ThisWorkbook.Sheets(1).Activate 'edit
ActiveWindow.ScrollRow = 2
End Sub

 

[mattyn]

Apologies if I am coming across a bit thick!! Managed to get the code to vertically scroll - but there is no horizontal scroll to that particular cell! Would that be possible?

 

[Tim_Excel_]

Perhaps you can insert your code so I can have an idea of what you are trying to achieve.

 

[mattyn]

Firstly thank you for taking the time to help me! It is appreciated. I don't have any coding - other than what you have posted.

However I will try to better explain what I want to do.

I have a worksheet, that has a vertical freeze panes line between columns F and G. This allows columns A - F to remain visible at all time, and the worksheet horizontally scrolls from column G rightwards.

As the worksheet data is very wide, I want to put a button or link somewhere in columns A to F (so that they are always visible) to give direct link to various cells on row 2. GJ2 is one such cell. This will allow the user to quickly gain access to the bit of the worksheet he needs to work on.

However when I do a normal hyperlink to cell GJ2, and click on it, that cell is now in the top right of the screen. I want the worksheet to horizontally scroll so that cell GJ2 is up against the freeze pane line - in effect right next to column F.

Does this explain it better?

Matt

 

[Tim_Excel_]

Matt,

that clears up things a lot!

I've found you a page that does exactly what you need. I hope you can adjust it to your workbook:
http://www.cpearson.com/excel/zoom.htm
Good luck!

 

[mattyn]

Awesome thank you.

I am bit of a code luddite so using what you posted above, linked me to this:

[COLOR=#00A0C8][FONT=inherit]Sub Appl_Goto()[/FONT][/COLOR]    Application.Goto Reference:=Worksheets("Sheet1").Range("A200"), Scroll:=True [COLOR=#00A0C8][FONT=inherit]End Sub[/FONT][/COLOR]

Now that means I can create a link and it does what I want. I simply change the range for the cell i need and hey presto, a new link, and a very simple to use "menu bar". So thank you.

Now, to put some cherries and icing on that cake, could I use a drop down menu list instead of a menu bar? Something a bit more interactive and take up less screen space?

Matt

 

[Tim_Excel_]

Matt,
I'm not sure what you mean with a menu bar. It's unclear what you're trying to achieve, so I wouldn't know what you are trying to accomplish with a "menu bar", where it's located and how it would be replaced by a drop down list.