I came across a problem recently where I had certain cells within an excel spreadsheet which updated a graph when they were double clicked on via a macro. The problem was that it was unintuitive to the end user that clicking on these links would update the graph.
After discussion with the customer, we decided that if they where to look hyperlinks it would be more intuitive that they actually did something when clicked on. “Simple!” said I, “I’ll have that fixed up before we next meet. I’ll just change their style to look like hyperlinks… colour them blue, underline them… easy peasy!”
So I had a play around with the formatting in Excel, googled frantically for 20 minutes and discovered this is not so simple. The problem was the entire cell was being underlined, not just the text. This is the best that I could come up with.
Not sure why this happened as I’m sure excel is supposed to allow partial underlining of a cell. There is a solution however and it involves a bit of VBA.
- Click Alt-F11 to go into the VBA screen.
- Double click on the sheet you’re working on as we’ll store the VBA code there.
3. Paste the following code into the sheet.
Public Sub Hyperlink_Months(bHyperlink As Boolean)
Dim lFirstRow As Long
Dim lLastRow As Long
lFirstRow = 2 'First Row Number
lLastRow = 13 'Last Row Number
Dim i As Integer
If bHyperlink = True Then
For i = lFirstRow To lLastRow
Sheet1.Range("A" & i).Characters(1, 3).Font.Underline = True
Sheet1.Range("A" & i).Characters(1, 3).Font.Color = 7884319
Next i
Else
For i = lFirstRow To lLastRow
Sheet1.Range("A" & i).Characters(1, 3).Font.Underline = False
Sheet1.Range("A" & i).Characters(1, 3).Font.Color = 0
Next i
End If
End Sub
4. Ensure the immediate window is displayed – CTRL + G
5. Run the sub by typing the following into the immediate window (assuming you stored the code in Sheet1)
sheet1.Hyperlink_Months(true) – To turn on the partial formatting of the text (making it look like hyplinks in this case)
or
sheet1.Hyperlink_Months(false) – To turn them off again.
6. Your life is now complete! 😉
