VBA Code needed to add a link to cell on another tab in the same excel file

[bbbb1234]

Hi Excel Gurus!!!

I simply cannot figure this out so I am looking for help on the following:

Workbook Setup
• Workbook Name: PatientList
• Sheet1 Name: Patients
• Enter "TestPatient1" in cell A1 on Patients sheet
• Right click on cell A1
• Click "Link" on the drop down and then click "Insert Link"
• On the left nav in the dialog box that comes up, click "Place in This Document"
• In the "Or select a place in this document:" box, select "Doe, John" sheet
• Sheet2 Name: Doe, John

What I Need
I need the exact VBA code in Excel that enables me to click on the Patients|cellA1 and it takes me to the "Doe, John" sheet. Effectively, it is the code that emulates doing the setup above.

Thanks in advance for your help!!!!

 

[Micron]

I figure this is not really what you want, but it is what you asked for. On Patients sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then Sheets("Doe, John").Activate
End Sub

 

[bbbb1234]

I figure this is not really what you want, but it is what you asked for. On Patients sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then Sheets("Doe, John").Activate
End Sub

Micron - you are correct - that is not what I am looking with my post. Let me make another pass at it that I hope will clear it up. I attempted several different approaches but did not keep the various VBA code or I would provide it in this post - mainly because it did not work.

The macro requirement are as follows:
A/ Prompt the user for the patient first and last names - - the example provided below uses John as the FirstName and Doe as the LastName
B/ Macro will enter the FirstName in the First Name column and the LastName in the Last Name column on the "Patients" sheet
C/ The macro will then create a separate sheet for logging notes in the same workbook about the patient using the LastName, FirstName of the user entry in step A
C/ The VBA code that is needed emulates what is described in the Workbook Setup section below for steps 2 through 7. Those steps do not use the HYPERLINK function to put in the cell where the LastName is but instead uses the "Place in this document" but I am not opposed to using the HYPERLINK function if that is what is needed. Said another way - once the macro completes, the user will be able to click in the name column and it will take the user to the log sheet in the same workbook.

Workbook Setup
1/ Workbook Name: "PatientList"
2/ Sheet1 Name: "Patients"
3/ Enter "TestPatient1" in cell A1 on "Patients" sheet
4/ Right click on cell A1
5/ Click "Link" on the drop down and then click "Insert Link"
6/ On the left nav in the dialog box that comes up, click "Place in This Document"
7/ In the "Or select a place in this document:" box, select "Doe, John" sheet
8/ Sheet2 Name: "Doe, John"

Thanks in advance for the assist!!!!

 

[bbbb1234]

Hi All-
I wanted to let everyone know that I finally figured this one out. Thanks to all who read this and gave it some noodle time. My apologies for the false start on this one.
...Brett