Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1595
  • Last Modified:

Positioning hyperlink target cell at center of screen

Hi,

I want to be able to click my hyperlink to another sheet and have the target cell appear at center screen.

Am using Excel 2010 and understand very little about macros etc.

Thankful for any help....

BTW: Happy New Year!   :)

0
MaKK
Asked:
MaKK
  • 6
  • 3
6 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
Place this code in the code module belonging to the worksheet containing the hyperlink:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    On Error Resume Next
    ActiveWindow.ScrollRow = Range(Target.SubAddress).Row - Int(ActiveWindow.VisibleRange.Rows.Count / 2 - 1)
    ActiveWindow.ScrollColumn = Range(Target.SubAddress).Column - Int(ActiveWindow.VisibleRange.Columns.Count / 2 - 1)
End Sub

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
To add VBA code to a worksheet or chart code module in an Excel workbook, right-click on the worksheet or chart tab at the bottom of the window and select View Code. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook. To find a worksheet or chart module when already in the VBE, press CTRL+R to open the VBE project explorer. Find the module in which the code will be placed - each worksheet and chart module is pre-assigned a name such as "Sheet1 (Sheet1)" where the name inside the parenthesis is the tab name. Double-click the desired module and paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

Kevin
0
 
MaKKAuthor Commented:
Hi Kevin,

Thanks muchly for a prompt reply!   I found your instructions easy enough to follow, however, sorry to say it's not working.  I saved the file, closed & reloaded Excel but the result was the same.

What now?  :)

Max



0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
zorvek (Kevin Jones)ConsultantCommented:
Make sure you save the workbook as a macro enabled workbook. Depending on your security settings you may have to also enable macros when opening the workbook.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
I was wrong. You are referencing a different worksheet and that wasn't handled with the code I gave you. This will work:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    On Error Resume Next
    Application.Goto Reference:=Application.Evaluate(Target.SubAddress)
    ActiveWindow.ScrollRow = Application.Evaluate(Target.SubAddress).Row - Int(ActiveWindow.VisibleRange.Rows.Count / 2 - 1)
    ActiveWindow.ScrollColumn = Application.Evaluate(Target.SubAddress).Column - Int(ActiveWindow.VisibleRange.Columns.Count / 2 - 1)
End Sub

Kevin
0
 
MaKKAuthor Commented:
Hi and thanks again,

  The workbook is saved as macro-enabled (xlsm). Tried the new code but without success.
Does it make any difference if the hyperlink is 'embedded' or a 'function' type?  I'm using the 'function' type.

  Max



0
 
zorvek (Kevin Jones)ConsultantCommented:
Yes, it does make a difference. The HYPERLINK function does not generate the FollowHyperlink event. And you can't emulate the HYPERLINK function with your own UDF.

Kevin
0
 
MaKKAuthor Commented:
Hi Kevin,

 I created an embedded link and bingo!  :)  

I've lots of links in my project and recently changed the sheet tab names.  As all  the links were originally 'embedded' had to convert all to 'function' so to globally edit the sheet tab names. Found a utility for doing this in ASAP Utilities  (which also recommends using 'function' for large numbers of links as "Excel can become slow").  So now, I'm looking for a method to globally convert the links back to 'embedded'.

Cheers,

Max

P.S. What's UDF mean please?



0
 
zorvek (Kevin Jones)ConsultantCommented:
UDF = User Defined Function.

Kevin
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now