5

When I used, in visual basic, the codes shown below, the HYPERLINK part appears in the formula bar, in the worksheet. I just want the "TextHere" to display in the formula bar.

What additional codes can I add? I've tried to hide it by changing its properties in the protection tab, but it does not allow me to edit the TEXT anymore. I would like it to still be edit-friendly.

Sub IndexingSheets()
    Sheets(1).Range("A1").Formula = _
    "=HYPERLINK(""#" & ThisWorkbook.Sheets(2).Name & "!A2"", ""TextHere"")"
End Sub
Community
  • 1
  • 1
kazSone
  • 95
  • 1
  • 3
  • 12

1 Answers1

17

How to hide a formula from the formula bar
Let me demonstrate two ways of hiding formulas from the formula bar

No1.

To hide the formula from the formula bar you have to set the HiddenFormula property the Range object to True
It will only work while the worksheet is protected
So the trick here is:
-> select all cells and unlock them for editing
-> select cells you want to hide formulas from and lock them
-> protect the sheet

Select all cells and unlock them for editing
-> select all cells, right click anywhere to format cells. Go to the Protection tab and unselect Locked unlocking

Select cells you want to hide formulas from and lock them
-> select A1, right click, go to Protection tab and select Locked and Hidden hide formulas

Protect the sheet
-> Click the Review tab, then Protect Sheet and OK ( no password necessary )
protect sheet

Now notice, you can still edit any cell except the A1. Look at the formula bar - There is no formula! Its HIDDEN!
done

This is a VBA solution:
Sub HideTheFormula()

    Dim ws As Worksheet
    Set ws = Sheets(1)

    Call IndexingSheets

    Call Setup(ws)
    Call ProtectSheet(ws)
    'Call UnprotectSheet(ws)

End Sub

Sub IndexingSheets()
    Sheets(1).Range("A1").Formula = _
    "=HYPERLINK(""#" & ThisWorkbook.Sheets(2).Name & "!A2"", ""TextHere"")"
End Sub

Sub ProtectSheet(ByRef ws As Worksheet)
    'ws.Protect userinterfaceonly:=True
    ws.Protect
End Sub

Sub UnprotectSheet(ByRef ws As Worksheet)
    ws.Unprotect
End Sub

Sub Setup(ByRef ws As Worksheet)
    With ws.Cells
        .Locked = False
        .FormulaHidden = False
    End With
    ws.Range("A1").Locked = True
    ws.Range("A1").FormulaHidden = True
End Sub

No2.

With a new spreadsheet insert this code in a new VBE(ALT+F11) Module. Execute the Main macro from the View Macros window (ALT+F8)

Sub Main()
    With Range("A1")
        .Formula = "=1+1"
    End With

    With Range("A2")
        .Formula = "=1+1"
        .Value = .Value
    End With
End Sub

After execution have a look at the sheets ranges A1 and A2
When A1 gets selected and you look at the formula bar you can see the formula =1+1,
however when you select A2 even though you have put a formula in the cell, it has been evaluated and hidden so now it displays the evaluated value (how cool!)
evaluated

The same principle applies when you are pulling a value from a closed workbook, for instance

Sub PullValueFromAClosedWorkbooksRange()
    With Range("A1")
        .Formula = "='C:\Users\admin\Desktop\[temp.xlsm]Sheet1'!A1"
        .Value = .Value
    End With
End Sub