Run time error 5 on some computers

  • Hi experts,


    I made the following VBA code to color some objects and add a screentip.
    It works fine on my computer and some computers of my colleagues, but it does not work with some other computers. In those cases, it would return Run time error 5 Invalid procedure call/argument.


    Are there anything wrong in the code below which would cause this kind of problems problems?


    Sub Color()
    Dim i As Integer
    For i = 1 To Range("pntMax")

    Range("pntOrder").Value = i
    'fills in shape
    ActiveSheet.Shapes(Range("pntShape").Value).Fill.ForeColor.RGB = _
    Range(Range("pntColor").Value).Interior.Color
    'adds screen tip
    ActiveSheet.Hyperlinks.Add ActiveSheet.Shapes(Range("pntShape").Value), "", "G20", ScreenTip:=Range("pntTextValue").Value


    Next i
    ActiveSheet.Range("pntOrder").Select
    End Sub



    On Debug, this line adding the screentip is highlighted
    ActiveSheet.Hyperlinks.Add ActiveSheet.Shapes(Range("pntShape").Value), "", "G20", ScreenTip:=Range("pntTextValue").Value

  • I have some additional information.
    This code is apparently not working for Excel 2010 (Windows), but works on my Excel 2016.
    However, I need to make it work with Excel 2010, and I would appreciate if anyone have any pointers to fix this.

  • Please try this:


    dim ST as string

    ST=Range("pntTextValue").Value

    if ST="" then ST=""

    ActiveSheet.Hyperlinks.Add ActiveSheet.Shapes(Range("pntShape").Value), "", "G20", ScreenTip:=ST

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!