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

[EliseFreedman]

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?

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

 

[SkipVought]

Elsie,

Put a Break in your code at...

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

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



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EliseFreedman]

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/getfil...b1d7-62b80a931851&file=TrainingReportFAQ.xlsm

 

[SkipVought]

Did you see my Jul 31 suggestion?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EliseFreedman]

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

 

[EliseFreedman]

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

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

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


End Sub

 

[SkipVought]

Great!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]