Why can't I change the font in a cell with VBA function that is triggered by a =hyperlink() formula?

Question

I am trying to change the text and its appearance in a VBA function (changeCell).

option explicit

function changeCell(txt as string, sz as long) as range

    cells(4,4).value      =  txt
    cells(4,4).font.size  =  sz
    cells(4,4).font.bold  =  true
    cells(4,4).font.color =  rgb(255, 100, 0)

    set changeCell = cells(5,5)

end function

When I call changeCell("foo", 20), the text is changed as I expected it.

However, If this function is called from a =hyperlink() worksheet function, the font.size and font.bold property is not changed (although font.color and .value is).

This is the function I used to insert the hyperlink on the sheet:

sub insertHyperlink()

    cells(2,2).formula = "=hyperlink(""#changeCell(""""hyperlink was clicked"""", 99)"", ""click me to change cell"")"

end sub

I am trying to use the =hyperlink() function because I need to be able to pass parameter values to the function that is invoked. These values are determined when the =hyperlink() function is inserted (which doesn't happen manually, but with a function like insertHyperlink, albeit more elaborate)

I am wondering why that is and how I can change the font size in a function that is called by =hyperlink().

Pᴇʜ · Accepted Answer · 2021-06-24T12:37:12.957

The following workaround is possible (even not the nicest):

We add a type for cell style and a public variale in a module.

Option Explicit

Public Type TCellStyle
    Text As String
    Size As Long
    Cell As Range
End Type

Public CellStyle As TCellStyle

Then use the following function that is called by your hyperlink =hyperlink("#changeCell(""hyperlink was clicked"", 99)", "click me to change cell")

Public Function changeCell(txt As String, sz As Long) As Range
    Dim ws As Worksheet
    Set ws = Application.Caller.Parent  ' get the worksheet of the hyperlink
    
    ' save the style to our public variable for later processing
    With CellStyle
        Set .Cell = ws.Cells(4, 4)
        .Text = txt
        .Size = sz
    End With
    
    ' set the destination of the hyperlink (must be in the same sheet as the hyperlink because this trigger the SelectionChange event).
    Set changeCell = ws.Cells(5, 5)
End Function

Then we just need a Worksheet_SelectionChange event in the desired worksheet and let that do the dirty work for us (that we could not do in changeCell directly because of the restriction):

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' only run this if there is a style to apply
    If Not CellStyle.Cell Is Nothing Then
        With CellStyle
            .Cell.Value = .Text
            .Cell.Font.Size = .Size
            .Cell.Font.Color = RGB(255, 100, 0)
            
            Set .Cell = Nothing  ' reset so it does not run again with every cell selection change
        End With
    End If
End Sub

The reason why changing some cell properties directly in changeCell does not work is because what the hyperlink does is the same as Evaluate(changeCell). If you check that it will be able to change the color but eg. not the font size with Evaluate. Therefore the workaround is needed.

I could see it in the meantime... I did not know that such a function can be called with parameters. Voted it up! — FaneDuru, Jun 24 '21 at 12:55
Thx, there is still room for improvement, instead of hardcoding the cell that is changed `Set .Cell = ws.Cells(4, 4)` you could even give it as a third parameter `CellToChange` with the link and `Set .Cell = CellToChange`. So this gets really generic and can easily be re-used. — Pᴇʜ, Jun 24 '21 at 12:58

FaneDuru · Answer 2 · 2021-06-24T12:21:43.247

1

Your question is interesting, on my taste. Such a function can change the value of another cell, but some cell format functionalities do not. Please, test the next code:

Function changeCell() As Range
    Set changeCell = Selection         'Do not miss this part!
    
    With changeCell.Offset(2, 2)
        .value = "Hyperlink was clicked"
        .Font.size = 20      'it does not work in such a function
        .Font.Bold = True  'it does not work in such a function
        .Font.Color = RGB(255, 100, 0)
    End With
End Function

Edited:

I tried a trick, using a sheet event, but not making it working with complex actions. Selecting the range, neither...

The code creating the hyperlink:

Sub insertHyperlink()
    cells(2, 2).Formula = "=hyperlink(""#changeCell()"", ""Sheet122|C1"")"
End Sub

The called function code:

Function changeCell() As Range
    Set changeCell = Selection         'Do not miss this part!

    cells(2, 6).value = changeCell.value
End Function

The event being triggered by the above function:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "F2" And Target.value <> "" Then
        Dim sh As Worksheet, rng As Range
        
        Set sh = Worksheets(Split(Target.value, "|")(0))
        Set rng = sh.Range(Split(Target.value, "|")(1))
        doSomething rng
    End If
End Sub

The called function:

Sub doSomething(rng As Range)
    Debug.Print rng.Address(external:=True), rng.Parent.Name
    Sheets(rng.Parent.Name).Activate
End Sub

It does not manipulate the objects as it should...

Maybe I am doing something wrong. I can accept that...

edited Jun 24 '21 at 12:21

answered Jun 24 '21 at 09:02

FaneDuru

38,298
4
19
27

@René Nyffenegger What do you mean by "passing an arbitrary number of arguments"? Like ordinary function arguments? You cannot do it directly, but you can build something from a string, let us say, being extracted from the cell value (cell addresses, names etc.). – FaneDuru Jun 24 '21 at 09:09
Currently, I have a "menu"-worksheet, that is dynamically created, depending on user using it and other factors, which presents the list of hyperlinks from which the user can choose from. These hyperlinks need to pass values to the function invoked. – René Nyffenegger Jun 24 '21 at 09:19
@René Nyffenegger Unfortunately, this hyperlink formula use has big limitations in dealing with ranges. You can create objects using the string from somewhere (for the hyperlink cel value, for instance), but you can only return date from such a built cell address, or write a string in it... – FaneDuru Jun 24 '21 at 10:45
@FaneDuru you can workaround by letting an event do the *"dirty"* work for you. See my answer below. – Pᴇʜ Jun 24 '21 at 11:54
@Pᴇʜ I am afraid, not... I tried it many times. I tried using `Worksheet_Change` event, being triggered by a specific cell change. The one modified by Hyperlink. It is triggered, I can set the objects extracted from string (Worksheet and Range), I can return the range value, but **if I try to only select the range**, of course, after sheet activating it fails. I think Microsoft tried protecting the user from some malicious code able to do more... – FaneDuru Jun 24 '21 at 11:58
@FaneDuru It worked as I did it below. The link triggers the function that takes the arguments and saves the information in a public variable. Then the `SelectionChange` picks ub the saved information and modifies the cell. So the only thing you need to do is selecting **any** cell in the sheet to trigger the `SelectionChange` event to pick up the work. I added a animation. – Pᴇʜ Jun 24 '21 at 12:04
@Pᴇʜ I also was able to send the argument to a function, transform the strings in objects, but like I said, I cannot do too much in that specific function except manipulating strings. I can show you the code I used... But how? Should I edit my answer? In fact, can you share your working code able to, at least, select the range if this exists in a different sheet than the one where the hyperlink has been clicked? – FaneDuru Jun 24 '21 at 12:15
@FaneDuru If you want to change cells in other worksheets just give the destination cell to change as another parameter of the `changeCell` function eg `CellToChange`. So if you have this formula in Sheet1 `=HYPERLINK("#changeCell(""hyperlink was clicked"", 99, Sheet2!B4)"; "click me to change cell")` you just need to set this cell to `Set .Cell = CellToChange` and `Set changeCell = CellToChange` and you need the `Worksheet_SelectionChange` in `Sheet2` then. The code in my answer should be used. – Pᴇʜ Jun 24 '21 at 12:23
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/234160/discussion-between-p-and-faneduru). – Pᴇʜ Jun 24 '21 at 12:26
@Pᴇʜ Theoretically, yes. You can change the value of any cell, but the question was about the possibility to send parameters to functions... I thought that building a range (object) and passing it to a function, the code may do whatever you want with it and it is not true... – FaneDuru Jun 24 '21 at 12:27

Why can't I change the font in a cell with VBA function that is triggered by a =hyperlink() formula?

2 Answers2