Error when hyperlink function used with offset function

[Surkelly]

Hi,


I'm trying to create a list (range of cells) where:

1) when the mouse hovers over it, the cell itself will be highlighted.

2) If it is selected, it will also highlight itself but into a different color (did this with an example on this website)

3) Finally, the string in the cell (which is linked to a dynamic source data) must be dynamic in reaction to a scroll bar.


I have posted a sample excel sheet to show what I've been able to do so far.


I have been able to do:

#1) using a hyperlink and conditional formatting

#2) with some vba and conditional formatting


However, I am having trouble with #3), i.e. getting the scroll bar effect in combination with the mouseover effect, and selection effect.


This is the function I used in the cell:

IfError(hyperlink(function(range),range2),range2)

where "function(range)" is a function coded in VBA in order to create the mouseover effect, and "range2" is the cell reference containing the offset function for the scrollbar.


However, I keep getting the error message: "Cannot open specified file" when I click on the cell. The problem only appears when range2 is referenced to a cell containing the offset function. If you delete list3 entirely, the worksheet works fine.


It's important that I am able to select and click on the cells because I want the form on the right hand side of the excel spreadsheet to be able to initialize with the values contained in the source data.


Thanks in advanced.


D/l the excel file here.

https://www.dropbox.com/s/ecnlsf4gdddeisc/Hyperlink%20Error%20Spreadsheet.xlsm

 

[Hui]

Surkelly


Firstly, Welcome to the Chandoo.org Forums


If we use D9 as a n example the Hyperlink is looking for a Link from the function Rollover(C9)


Now the value in C9 is 2


The Rollover UDF makes the Named Formula Rolloverrow = to the value in C9 or 2

But the Rollover isn't returning a value to the Hyperlink formula as Rollover itself isn't set


so I think the Rollover UDF should have something like:

[pre]

Public Function Rollover(row As Integer)
Range("rolloverrow").Value2 = row
Rollover = Link
End Function

[/pre]
Where the last line Rollover= will be set to a valid link address which will be passed back to the function in D9

 

[Surkelly]

Hi Hui,


Thank you for the reply. I tried that as well but not working.


This is the new line of code I tried.


Public Function Rollover(row As Integer)

Range("rolloverrow").Value2 = row

Rollover = "[Hyperlink Error Spreadsheet.xlsm]Alternatives_Form!A1"

End Function


If the cell formula was just "=hyperlink(rollover(C9),"")", the link works so it's not in the vba. However, I need the iferror function, i.e. "=IFERROR(HYPERLINK(rollover(C9),G9),G9)" to display the string on the cell.

The problem happens when G9 contains an offset formula (required for the scroll bar), i.e. "=OFFSET(Alternatives_Sourcedata!A3,Alternatives_Form!$A$8,0,1,1)"


Is there a way to bypass the error message then? I was wondering if that was what was stopping the macro from running.


Thanks!

 

[NARAYANK991]

Hi ,


I do not know whether you have resolved your problem ; if you haven't are you still interested in getting a solution ?


If so , rather than asking forum members to debug an application , can you specify what your objective is ?


I have not gone through your workbook in detail , but it seems to me that there are too many issues to distract one's attention ; can you say exactly what is your one point that needs to be resolved ?


I can understand that the IFERROR(HYPERLINK(...)) construct is creating a problem , but what is the context ?


Narayan