MACRO HELP: Open Hyperlinked File in row below the current one, then move activecell.

[jeffcoleky]

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr> <tr><td>1</td><td>Released</td><td>Date</td><td>First</td><td>Last</td><td>Parties</td><td>Description</td><td>File</td><td>Plaintiff</td><td>Street Name</td><td>Zip Code</td></tr> <tr><td>2</td><td>No</td><td>01/04/2011</td><td>Bob</td><td>Northington</td><td>NORTHINGTON, BOB</td><td>11CI400005 RANGELAND SUB SEC 8 LOT 5</td><td>2011001694</td><td>Martha Smith</td><td>123 Count Rd</td><td>80126</td></tr> <tr><td>3</td><td>No</td><td>01/04/2011</td><td>Patty</td><td>Purnell</td><td>PURNELL, PATTY</td><td>11CI400011 BAY COLONY SUB SEC 3 LOT 5</td><td>2011001695</td><td>Jamie Walker</td><td>456 Sam St.</td><td>80122</td></tr> <tr><td>4</td><td>No</td><td>01/04/2011</td><td>Ron</td><td>Albertson</td><td>ALBERTSON, RON J</td><td>11CI400095 BARBOUR MANOR SEC 4-A LOT 255</td><td>2011001696</td><td>Mortgage Servicing LLC</td><td>789 Sesame St.</td><td>80124</td></tr></table>

I am trying to speed up some data entry. The data in Columns A-G is already populated, and the user must fill in H-J.

Every cell in Column "G" contains a hyperlink to a PDF on the local hard drive.

Here is how the user inputs the data:

1. Click on the hyperlink in Column "G" using the mouse.
2. User navigates the mouse & clicks on the cell to the right of the hyperlink in column H.
3. The PDF pops up and the user inputs the data into H, (TAB), I (TAB), J (TAB) from the PDF manually.
4. The user presses "ENTER" and moves the cursor to Row H on the line below.

What I wish to change it to is:

1. User places activecell in any given row of a cell in column K. (EG. K2)
2. User presses a macro and it opens the hyperlink in Column G of the row BELOW. (EG. G3)
3. After the hyperlink is opened, the active cell is automatically placed on Column H of the same row of the hyperlink that was just opened, in preparation for the user to input the data. (EG. H3)
4. User inputs the data into Column H, (TAB), I (TAB), J (TAB) manually (EG. H3, I3, J3).
5. Cell then ends up back in column K because user presses TAB instead of ENTER after inputting data into J3. (EG. K3)
6. User presses macro again and the process repeats for the next row (EG. Row 4).

The annoyances are that a) they have to use the mouse to click on the hyperlink. b) Pressing ENTER doesn't always move the cursor to Column H on the next line. While it seems a small thing, using a MACRO to do this would be extremely useful.

The Genius that can help me figure this out will get 10 brownie points...no wait, wait.... make that 1000 brownie points. Yes, I am very generous, just not very Excel smart

Alternatively, if you can come up with a way to create a keyboard shortcut that opens the hyperlinke of the ACTIVECELL, that would be the next-best solution...

 

[jeffcoleky]

I figured SOME of it out... this will open the hyperlink and then move the cell to the right one. However, I'm not sure how to have it open the hyperlink one row down then afterward move the active cell to the right 1.

Sub Open_Hyperlink()
'
' Open_Hyperlink Macro
'
' Keyboard Shortcut: Ctrl+o
'
 If ActiveCell.Hyperlinks.Count Then
        ActiveCell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
        ActiveCell.Offset(0, 1).Range("A1").Select
    Else
        MsgBox "Please select a hyperlink.", vbCritical, "Not a Hyperlink"
    End If
End Sub