Run-time error '-2147417848(80010108)' Method Value of object range failed

beedistinct

Board Regular
Joined
Jun 24, 2011
Messages
62
Code:
Sub CnvToHyper()
'
' Macro1 Macro that converts a display to Hyperlink enable so that
' Mouse on Hover works.
'
 Dim display As String
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
 Sheets("TestHover").Activate
 With ActiveSheet
    For i = 1 To 0 Step -1
       For j = 6 To 0 Step -1
          [FirstDisp].Offset(i, j).Select
          display = ActiveCell.Value
          [COLOR=#ff0000][B]ActiveCell.Value = "=IFERROR(HYPERLINK(MouseOver(""" & display & """)),""" & display & """)"[/B][/COLOR]
       Next j
    Next i
 End With
 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic
End Sub

It is giving the error on the statement in red.
I'm trying to convert a data that is 2 rows 6 columns wide such that each cell has a formula as listed in red above. Am using Excel 2013 with no add-ins and am about to go nuts. What am I doing wrong here??!! Please help. Each cell in the data contain an alpanumeric ID 12 chars long and nothing else.

Here is the MouseOver function
Code:
Public Function MouseOver(PassedValue As String)
    If [ErrBox] = PassedValue Then Exit Function
    [ErrBox] = PassedValue
End Function
 
Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
I can run that code without error, and it does insert the correct formulas, though obviously my data is completely different from what you have.

Could you upload a sample workbook to somewhere like Box.net and then post a link to it here?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
I've tried various things but can't seem to find what the problem is.

I do have an inkling that it might be something to do with the MouseHover UDF and/or circular references.

UDFs are normally used to return a value as a result to the cell they are in, not setting value in other cells.

If I change the UDF to this everything works without error though I doubt very much it does what you want.
Code:
Public Function MouseOver(PassedValue As String)
  MouseOver = PassedValue
End Function

Actually, what is it you are trying to do?
 

beedistinct

Board Regular
Joined
Jun 24, 2011
Messages
62
I've tried various things but can't seem to find what the problem is.

I do have an inkling that it might be something to do with the MouseHover UDF and/or circular references.

UDFs are normally used to return a value as a result to the cell they are in, not setting value in other cells.

If I change the UDF to this everything works without error though I doubt very much it does what you want.
Code:
Public Function MouseOver(PassedValue As String)
  MouseOver = PassedValue
End Function

Actually, what is it you are trying to do?

Thaks for your help. Were you able to reploicate the problem/run-time error?

I'm trying to do a Vlookup on the cells when someone hovers a mouse over it. So, basically, I have a bunch of cells with some values in it. I want the user to see those same values but when he moves the mouse over the cell then it uses the value that is displayed in the cell to do a simple vlookup.
The strange part is that if I manually type the HYPERLINK formula pasted above in all my cells where I want the hover to work, it works fine! It is just that when I'm using VBA to introduce the formulae in those same cells, I'm getting the problem.
 

Forum statistics

Threads
1,136,303
Messages
5,674,966
Members
419,537
Latest member
ucatchy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top