jump to specific column but not change rows?

merlin777 · Aug 16, 2014

I have a very large sheet and I want to navigate quickly to columns. I was intending to use the hyperlink function but the problem is, although they'll jump me to the right column, I have to specify a target cell which means the rows will change to.

Is there a way just to jump left and right without moving columns? I know I can navigate with combinations of the paging buttons but I'm setting the sheet up for novice users so I want to give them an onscreen button to click. It'll be more exact too.

Snakehips · Aug 16, 2014

Merlin777

How do you expect to identify the column you wish to navigate to?

Do you just want a jump 5 or 10 or so columns right or left?

merlin777 · Aug 16, 2014

I have a group of 13 columns for each day of the week (Mon to Fri only) so I'm linking to the first of the 13 columns in each weekday so I can jump from day to day.

At the moment I have this:

=HYPERLINK("#TEAM_DIARY!$E$7:$BS$7","JUMP TO MONDAY")

The additional ':$BS$7' is a trick to force the cell that is linked to, to be far left of the screen. Otherwise I find the positioning is a bit unpredictable. That cell is a few columns to the right of the last used column.

If I had a way of finding which row was at the top I might be able to put that into the hyperlink function so the row doesn't move. (To be specific, my top 6 rows are frozen so I want to know which row is at the top of the unfrozen section).

Snakehips · Aug 16, 2014

You can determine the current row below the freeze with eg

Code:

 MyRow = ActiveWindow.ScrollRow

merlin777 · Aug 16, 2014

Thanks, Tony. How can I combine that with the hyperlink function?

Snakehips · Aug 16, 2014

Not sure how you are planning to have Hyperlinks? In cells or with buttons ?

Either way won't they be lost on say the left of your sheet when you are scrolled to the right?

The below has that issue but does it spark ideas or have some value.....

Copy code to sheet module. Assumes your Hyperlinks in cells A1:A5
The destination cell for each is the cell itself so foe A1 it is $A$1
So it goes nowhere but runs the scroll code to the correct column.

Code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Select Case Target.Range.Address
Case "$A$1"   ' Monday
MyCol = 5
Case "$A$2"   'Tuesday
MyCol = 18
Case "$A$3"   'Wednesday
MyCol = 31
Case "$A$4"   'Thursday
MyCol = 44
Case "$A$5"   'Friday
MyCol = 57
Case Else
End Select
ActiveWindow.ScrollColumn = MyCol


End Sub

Otherwise code for buttons (Shapes) with assigned macro rather than hyperlink.

Make 5 shapes and have text Monday Tuesday etc then assign the macro Day_Column to each of them.

Code:

Sub Day_Column()
MyName = Application.Caller


MyDay = ActiveSheet.Shapes(MyName).TextFrame.Characters.Text


Select Case MyDay


Case "Monday"
MyCol = 5


Case "Tuesday"
MyCol = 18


Case "Wednesday"
MyCol = 31


Case "Thursday"
MyCol = 44


Case "Friday"
MyCol = 57
End Select


ActiveWindow.ScrollColumn = MyCol


End Sub

Could code for the buttons to move such that they are always visible but that would be tomorrow.

Bed beckons!!

merlin777 · Aug 16, 2014

you're very perceptive, Tony! What I do is put the links in cells using the hyperlink function and then cope and paste them at the top of the start of each day. That way, they never seem to move and there's always links on display to go to any day.

I'll take a look at the rest of your post in the am... my bed beckons too!

Snakehips · Aug 17, 2014

Why I think about Excel in my sleep I do not know but I do....
It dawned on me that your header area would probably allow for a set of hyperlinks per day.
So assuming you have links in E1:E5 R1:R5 AE1:AE5 and all link destination are to the host cell. Make the text to display Monday through Friday 1 to 5 in all cases.

Then this code.......

Code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)


Select Case Target.TextToDisplay
Case "Monday"
MyCol = 5
Case "Tuesday"
MyCol = 18
Case "Wednesday"
MyCol = 31
Case "Thursday"
MyCol = 44
Case "Friday"
MyCol = 57
Case Else

MyCol = 0
End Select
If MyCol > 0 Then ActiveWindow.ScrollColumn = MyCol


End Sub

That should sort it.

merlin777 · Aug 17, 2014

aha! I see what's going on - thanks, Tony.

I need a slight mod...

My links for mon-fri go along the row rather than column so E1:I1 and repeated on row 1 at 13 cell intervals.

There's also an unusual thing going on that i'm sure never happened before. When i type "monday" in any cell it auto corrects to "Monday". What's going on there?

merlin777 · Aug 17, 2014

oooh - last thing, while i have your attention, I'm trying to do something similar to scroll vertically but not horizontally. I have groups of 194 rows going down the sheet. Each group is a week from 1 to 53 so it's pretty long. I can't have 53 buttons so i wanted to select the week number from a cell and hyperlink to 194*selected week number (plus an offset for header rows).

is this technique suitable for this?

jump to specific column but not change rows?

merlin777

Well-known Member

Excel Facts

Snakehips

Well-known Member

merlin777

Well-known Member

Snakehips

Well-known Member

merlin777

Well-known Member

Snakehips

Well-known Member

merlin777

Well-known Member

Snakehips

Well-known Member

merlin777

Well-known Member

merlin777

Well-known Member

Similar threads

Forum statistics

Share this page

jump to specific column but not change rows?

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock