INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel - Hyperlink and updating values in another cell on the worksheet

Excel - Hyperlink and updating values in another cell on the worksheet

(OP)
Hi There

I am trying to create a FAQ which will sit in an excel report that we have developed.

Ideally, I want the user to be able to click on the hyperlink for the question that they want to know the answer to. The question will then be displayed on another area in the worksheet with the answer displayed below.

I am using the following code but although it does move the cursor to the cell required, it doesn't update the value. Not sure where I am going wrong?

CODE

Dim GSourceCell As String

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    
    'Update cell B2 in Destination sheet based on the origin of hyperlink
    If Sh.Name = "Sheet1" Then
        If GSourceCell = "C8" Then
            Sheets("Sheet1).Range("J8").Value = "What Courses Do I need to Complete?"
        ElseIf GSourceCell = "C9" Then
            Sheets("Sheet1).Range("J8").Value = "How do I access the courses"
        ElseIf GSourceCell = "C10" Then
            Sheets("Sheet1).Range("J8").Value = "I have already completed the courses but am still getting invites"
        Else
            Sheets("Sheet3").Range("J8").Value = ""
        End If
    End If
    
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Sh.Name = "Hyperlinks" Then
        'Capture last active cell on Hyperlinks worksheet and store in global variable
        GSourceCell = Target.Address(False, False)
    End If
    
End Sub 

RE: Excel - Hyperlink and updating values in another cell on the worksheet

Elsie,

Put a Break in your code at...

CODE

'
    If Sh.Name = "Sheet1" Then
'..... 

I think your Sh.Name is not "Sheet1" but "Hyperlinks"

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel - Hyperlink and updating values in another cell on the worksheet

Personally, I'd use a lookup table. The lookup value could be the text in the selected cell and the help text would be in the lookup table adjacent to the lookup text. Its usually not a good idea to embed data in code. Tables are much easier to maintain than code.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel - Hyperlink and updating values in another cell on the worksheet

(OP)
Hi Skip

I have used a lookup table to "look up" the answer to the question that has been selected by the user but I don't think that would work for working out which question they have selected and displaying it elsewhere on the spreadsheet

On the left of my sheet, I have got a panel with all the possible questions broken down by category. ive then got a centre panel where I want to display the question that has been selected from the left hand panel along with the answer to the question. It is the displaying of the question that I am having the problem with. I don't know how to work out which question the user has clicked

I have uploaded a copy of my file so you can see what I mean

http://files.engineering.com/getfile.aspx?folder=9...

RE: Excel - Hyperlink and updating values in another cell on the worksheet

Did you see my Jul 31 suggestion?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel - Hyperlink and updating values in another cell on the worksheet

(OP)
My workbook does not have a spreadsheet called hyperlinks in it. It just has Sheet 1, sheet 2 etc. The hyperlinks and panel are all on Sheet1

RE: Excel - Hyperlink and updating values in another cell on the worksheet

(OP)
I finally cracked it
realised that I was using workbook follow hyperlink instead of worksheet followhyperlink

once I was using the right method it was easy. Get the value of the question that was clicked. Hide the value in a cell then use vlookup to get the appropriate question

CODE

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim Question As String

    Question = Target.Range.Value
    Range("H6").Value = Question


End Sub 

RE: Excel - Hyperlink and updating values in another cell on the worksheet

Great!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close