Macro to Adjust Screen After Selection

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,214
Office Version
  1. 365
Platform
  1. Windows
In a sheet called "Summary" I have some hyperlinks to various other sheets. In some instances, once I click the hyperlink it takes me to the referenced cell in the respective sheet, however the referenced cell is at the bottom of the screen (because there are other data above it) and I have to scroll my mouse down so that the reference cell is on top of the page.

Is there a macro to adjust the screen so that when I click the hyperlink it shows the referenced cell on the top of the screen instead of the bottom?
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Is there a macro to adjust the screen so that when I click the hyperlink it shows the referenced cell on the top of the screen instead of the bottom?
1. Right click the sheet name tab of the Summary sheet and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  ActiveWindow.ScrollRow = ActiveCell.Row
End Sub
 
Last edited:
Upvote 0
1. Right click the sheet name tab of the Summary sheet and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  ActiveWindow.ScrollRow = ActiveCell.Row
End Sub

I tested your method too. When I click a hyperlink it takes me to the reference/active cell on the other sheets but it does not show the reference/active cell on top of the screen still.
 
Last edited:
Upvote 0
.
Thanks for asking the question. I learned something new also.

Cheers
 
Upvote 0
I tested your method too. When I click a hyperlink it takes me to the reference/active cell on the other sheets but it does not show the reference/active cell on top of the screen still.
1. Do you have macros enabled?

2. Did you follow the steps exactly? If the code ended up in the wrong place, it will not work. If you open the workbook again and right click the 'Summary' sheet tab name and choose 'View Code' you should immediately see the code.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top